Find last cell with data

  • Thread starter Thread starter Magnus Blomberg
  • Start date Start date
M

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
 
Magnus,

you could try

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

which will give you the last row in column A

Neil
 
Neil said:
Magnus,

you could try

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

which will give you the last row in column A

Neil
Click to expand...

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
 
If you go to the link in Frank's post, you will see that is one of the
slowest ways to do it.
 
Bob said:
The number is 65536 not 56550, or RowsCount to use a constant.
Click to expand...
And both, as well as Frank Kabel's suggestion, will fail if there is
data in the 65536th row.

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