Find last cell with data

[Magnus Blomberg]

Hello!

I have a problem about finding the last row in a sheet.

I am trying to use the code below to find my last row, but since the users
should edit my excel sheet before this script will be run, the last row
could be a complete different one than the row number returned:

Cells.SpecialCells(xlCellTypeLastCell).Row

A clearifying example: If the user gets 100 rows from a database. Then the
user deletes the last 20 rows, I want the code to return 80 instead of 100
which the code above does. It should not matter if the user removes the data
from for example row 45 and row 68. It should simply return the row number
for the last row where data exists.

I can't find a simple solution to fix this. Tried to walk though all rows,
but didn't find a simple way to find if a row where empty from data.

Regards Magnus

 

[Guest]

Hi
see:
http://www.xldynamic.com/source/xld.LastValue.html#vba

 

[Neil]

Magnus,

you could try

cells(56550,1).end(xlup).row

which will give you the last row in column A

Neil

 

[Alan Beban]

Magnus,

you could try

cells(56550,1).end(xlup).row

which will give you the last row in column A

Neil

This won't work if there is data below Row 56550, just as Frank Kabel's
suggestion won't work if there is data in Row 65536.

Alan Beban

 

[Bob Phillips]

The number is 65536 not 56550, or RowsCount to use a constant.

 

[Daniel Rascoe]

I found this link helpful in one of my spreadsheets
http://www.pcmag.com/article2/0,1759,1601676,00.asp

Daniel

 

[Tom Ogilvy]

If you go to the link in Frank's post, you will see that is one of the
slowest ways to do it.

 

[Alan Beban]

The number is 65536 not 56550, or RowsCount to use a constant.

And both, as well as Frank Kabel's suggestion, will fail if there is
data in the 65536th row.

Alan Beban

 

[Magnus Blomberg]

Hello!

Thanks all of you. I solved by using Franks solution, even if some of
you says it's not the best. It works for me.

I think it's time for Microsoft to add a function for either get the
last row/cell with data or to reset the xlCellTypeLastCell variable.

Thanks all
/Regards Magnus

 

[Tom Ogilvy]

xlCellTypeLastCell does exactly what is is designed to do. Perhaps what you
want is a function that returns the last cell that appears to contain data -
although that can be ambiguous (which is probably why there isn't one).

If you need a screwdriver and you use a hammer, many times the results are
not what you want.

 

[Magnus Blomberg]

Hello!

I understand the function, and are not saying it does something wrong,
but as I wrote, I still think MS could add a SpecialCell called
xlCellTypeLastCellWithData or something, or maybe a function that
recalculates/resets the last row.

Regards Magnus