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

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

"Magnus Blomberg" wrote:

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
"Magnus Blomberg" wrote in message
...
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

Neil wrote:

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

"Magnus Blomberg" wrote in message
...

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

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

--

HTH

RP

"Neil" wrote in message
...
Magnus,

you could try

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

which will give you the last row in column A

Neil
"Magnus Blomberg" wrote in message
...
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

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

Daniel
----------------------------------------------------------------------------
"Magnus Blomberg" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


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

Daniel
--------------------------------------------------------------------------
--
"Magnus Blomberg" wrote in message
...
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

Bob Phillips wrote:

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

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.

--
Regards,
Tom Ogilvy


"Magnus Blomberg" wrote in message
...
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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!