Last filled Column in a Worksheet

Hi all!

Pls cud some1 tell me how to find the last filled column 
in a Excel worksheet?

The xlCellTypeLastCell() gives the last TYPED cell,which 
is not always the last filled cell. 

Is there a way out?

Anita.
0
mithaas22 (2)
10/13/2003 4:42:39 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
297 Views

Similar Articles

[PageSpeed] 5

Anita

Sometimes this is a little tricky, due to Xl's reluctance to set it's
UsedRange after cells have been used and cleared.

I normally use a line similar to

iLastCol = Range("IV1").End(xlToLeft).Column
-- 
HTH
Nick Hodge
Southampton, England
nick_hodge@btinternet.com


"Anita K" <mithaas22@yahoo.com> wrote in message
news:07c801c39144$6e95f1b0$a101280a@phx.gbl...
> Hi all!
>
> Pls cud some1 tell me how to find the last filled column
> in a Excel worksheet?
>
> The xlCellTypeLastCell() gives the last TYPED cell,which
> is not always the last filled cell.
>
> Is there a way out?
>
> Anita.


0
nick_hodge (41)
10/13/2003 6:34:58 AM
I, too, like what Nick did (chose a row that always has data and use that), but
sometimes this works ok:

dim LastCell as Range
dim dummyRng as range

with worksheets("sheet1")
  set dummyRng = .usedrange
  set lastcell = .cells.specialcells(xlcelltypelastcell)
end with

Just refering to the .usedrange sometimes forces xl to let go.

Debra Dalgleish has some more techniques to help reset those sticky ones at:
http://www.contextures.com/xlfaqApp.html#Unused

Anita K wrote:
> 
> Hi all!
> 
> Pls cud some1 tell me how to find the last filled column
> in a Excel worksheet?
> 
> The xlCellTypeLastCell() gives the last TYPED cell,which
> is not always the last filled cell.
> 
> Is there a way out?
> 
> Anita.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/13/2003 11:20:58 PM
Reply:

Similar Artilces:

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

can you make one worksheet update another
I have two similar worksheets within the same workbook. One sheet is sorted by name and the other sheet is sorted by a date for a speicfic name. Is there a way to change the date on the sheet, sorted by name, and that will automatically find the name on the other sheet(sorted by date) and change the date to the second sheet? Jaime, The normal way is to have one table, and sort it as needed when needed. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jaime S." <Jaime S.@discussions.microsoft.com> wrote in mess...

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

how to use TrackPopupMenu last parameter
Hi, I need to make a menu that does not disappear. (Odd, yes. I need to let users edit menu resources at runtime and would like to display the menus as how they would actually look.) So I'm looking at CMenu's TrackPopupMenu function, whose last parameter is LPCRECT lpRect. I figure if I set this rectangle to be (0, 0, screen width, screen height), then even if the user clicks elsewhere on the screen, the menu won't disappear, right? I tried the following code and the menu still disappears when I randomly click elsewhere in the application. What am I doing wrong? void CMenuVi...

Help With Worksheet Operations
Is there any way in vba code to reproduce the same functionality in Excel as pressing Ctrl+right and/or Ctrl+down? I need a quick way to move to the last column and the last row on a spreadsheet. Also, is there a quick way to convert the worksheet to a string array or anything else (maybe a dataset)? Try Some Like this Sub MovetoEndDown() Range("A1").End(xlDown).Activate '(xlToRight) in case you want to move to the right. End Sub or Sub MoveDownPlus1Row() Dim NextRow As Variant NextRow = Range("A20000").End(xlUp).Row + 1 Cells(NextRow, 1).Activate End Sub Hope thi...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Error that prevents Copy Worksheet to another Tab
When I try to copy a worksheet as another tab in the same file or to another excel file, I get the error listed below. I can move the worksheet, I just can not copy it. Microsoft Office Excel cannot access the file ‘C:\DOCUME~1\JEFFRE~1.JEF|LOCALS~1\TEMP”. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. Bullet point one has not meaning since at this point I am not trying to open a file. The program has not problem opening fi...

Saving & Opening excel file & worksheet with Visual C++
I writing a sw application with Visual C++ to read/write data to excel file. I have found an example @ the MSDN on how to create a new excel file (http://support.microsoft.com/?scid=kb;en-us;308407&spid=2990&sid=620) using automation method. My question is how do I save/open an excel file using automation method. Thanks, WL ...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Worksheets stutas meaning
Hi All, Does any body have a translation for the Worksheets stutas:- Compelet with error (Red worksheet) Compelet with warning (Pink Worksheet) some times the worksheet comeleted at store and the other gives error or warning, in case of transfers Worsheets, a store issue out the Qtys and the other store does not receive the transfer on the system, and gives in the HQ error or warning. Is there any other idea to aviod perform that transfer again? What those are mean, when error or warning happens, and why? I hope Microsoft add a decriptive error or wraning to let the user know what hap...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

Columns in Money 2004
Is there any way to change the default columns in the register view ? I really dont want the flagged column, and would like to include a column for categories. Is it possible to do this ? In microsoft.public.money, VerticalCobra wrote: >Is there any way to change the default columns in the register view ? I >really dont want the flagged column, and would like to include a column for >categories. Is it possible to do this ? No. Try Ctrl+T to toggle to multi-line display and back. ...

Getting info from the last used cell
Currently I am adding many sheets together. I am using "Start Sheet" and "End Sheet" and everything in between those two sheets is adding for a total. I would (in a few cells) to just get the data from the last cell used. Example: The sheets I am adding are dates and if sheet 01-19-09 (dd-mm-yy) has the last entry in cell c18 I would like that number. However there may be blank cell before that sheet. and after that sheet there will be info in other cells. I would like that info to go into c18 of the sheet I use and named "Totals". -- Ed Davis ...

Retrieving the Top 10 of the Average of Two columns, but displaying a third
I'm new to Excel programing, so please bear with me. I have an excel spreadsheet with 3 columns. Column1 Column2 Column3 -------------------------------------- John Doe 5 3 Jane Smith 2 9 Bill Smith 4 2 What I need to do is take the average of Column 2 and 3, find the highest average of the entire list and display Column 1 as the final result. In other words: I need to create a Top 5 List of the people with the highest grade average. I don't care much for displaying the grade, I just need the top 5...

how do i "de-dupe?" rows by a column value?
I have a complex operation to perform. My spreadsheet has rows that indicate items (control number in column A) linked to titles (control number in column B). A title can have multiple items in the spredsheet. I want to number the titles and assign random numbers to them so that I can get two random samples. I also want to have my random numbering retained in the spreadsheet as a whole, so I can see all the items assigned to my samples. How can I "dedupe" column B, assign a random number to each title control, then transfer that data to my "undeduped" spreadshe...

Need to retrieve a previous version of an excel worksheet
Help! I've been working on an excel sheet for a few hours and as I was entering a formula, I rec'd an error message that stated that Excel would be closed down. Now when I open the document it is the old version (before the hours of work). Is there a way to retrieve the most recent version? No, if you didn't save it, it disappeared when Excel disappeared. -- HTH RP (remove nothere from the email address if mailing direct) "NancyDrew" <NancyDrew@discussions.microsoft.com> wrote in message news:98F82775-EF72-4A2D-B9F6-4537D31CB327@microsoft.com... > He...

Query about Last Cell reference.
I have 12 sheets, which holds monthly information. Data exists in Column A through K, row 1 down to row 500. I have a formula in cell AZ1 (done to ensure no one can find it and subsequently delete it). When I depress CTRL+END it takes me to AZ500 (as expected). The question I have is that as all my data is going to be in Columns A through K and rows 1 to 500, if I reset the Last Cell to say K500 would it achieve faster saves to a LAN?. Please note that the value from AZ1 is used in column G1 – G500. All views greatly appreciated. First, you can olnly "reset" the last cell ...

Can I change line numer or column name
I'd like to change my line numbers to specific salespeople and column names to specific months. Does anyone know if thats possible and how to do it? Thanks Dave, you can't change the ones in excel but you can put your own in row 1 and column A, then you can hide the row and column headers if you want, tools options, view, uncheck row and column headers -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave Bonneville&q...

copying worksheet problem
I get the warning--No more new fonts maybe added to this workbook--when I make a copy of a worksheet that contains graphs. It is not a large file. And when the new worksheet appears--some the fonts are much larger. Any ideas? Check out this http://support.microsoft.com/default.aspx?scid=kb;en-us;215573 XL2000: Error Copying Worksheets Containing Charts -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "mel" <anonymous@discussions.microsoft.com> wrote in message news:2d8e401c394c2$b23cef50$a601280a@phx.gbl... > I get the warning--No more n...