Last filled Column in a Worksheet

  • Thread starter Thread starter Anita K
  • Start date Start date
A

Anita K

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.
 
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
 
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
 
Back
Top