My code keeps returning original number of rows when rows have been deleted!

[tungchiu]

Hi clever people,
I am using the following to determine the last row on my data sheets. It works fine but if rows are deleted, the code will still return the original number of rows??? What can I do please.

lstRw = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

Cheers

 

[Nalani]

Not knowing Exactly what you are doing,

lstRw = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

will not reset until the workbook is Saved.

 

[tungchiu]

Not knowing Exactly what you are doing,

lstRw = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

will not reset until the workbook is Saved.

Thanks heaps Nalani - I understand now.

 

[Nalani]

If you are trying to Reset lstRw after you delete rows, within your macro,

Maybe posting your whole code with an explanation will help in determining what you are trying to do

 

[cgcamal]

Hi tungchiu,

How Nalani says, this is a behaviour of "xlCellTypeLastCell" is needed to save first the workbook to reset the used range. See this short article:

Automatically Resetting the Last Cell

Also you can use another options, for example, like these:

Range("A1").End(xlDown).Row

or

Range("A65000").End(xlUp).Row

Regards

 

[Rick Rothstein]

Also you can use another options, for example:

Range("A65000").End(xlUp).Row

@César,

A better way to do the above would be like this...

=Cells(Rows.Count, "A").End(xlUp).Row

that way, you can use the code in any version of Excel (yours is limited to XL2003 or earlier).

 

[cgcamal]

Hi Rick,

Many thanks to share it. I'll use it that way.

Best regards

 

[Nalani]

@César,

A better way to do the above would be like this...

=Cells(Rows.Count, "A").End(xlUp).Row

that way, you can use the code in any version of Excel (yours is limited to XL2003 or earlier).

And another way that I do it,

lstRw = Range("A"& Rows.Count).End(xlUp).Row

This is why I ask the OP to post the whole code for a quick look at what is wanted / needed. Post #4

 

[Rick Rothstein]

Hi clever people,
I am using the following to determine the last row on my data sheets. It works fine but if rows are deleted, the code will still return the original number of rows??? What can I do please.

lstRw = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

Cheers

@tungchiu,

As you discovered, the LastCell method can be fooled. Other posts in this thread have shown you how to get the last row number when you know which column will contain that last piece of data. However, if you do not know which column will hold the last piece of data, then you can use one of the following methods depending on your setup and/or needs...

' For last value only
' ==================================
LastUsedRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
 
' For last value or formula 
' ==================================
LastUsedRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

Note the use of the LookIn argument. When LookIn is set to xlValues, it will ignore formulas that are displaying the empty string and locate the last cell with a displayed value in it (whether the value is manually entered or from a formula). On the other hand, when LookIn is set to xlFormulas, it will locate the last cell with either a manually entered value or a formula in it even if that formula is displaying the empty string. Which setting you use for LookIn depends on what your code is trying to do, but it can be very important if your are, let's say, placing data/formulas after the last cell if you want to make sure you don't overwrite formulas that are not currently displaying values.

 

[AlphaFrog]

Another way is to use...

lstRw = ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row

• Pros: finds the last used row on the sheet irregardless of which column has the last used row.
• Cons: errors if the sheet is completely empty.

There are several ways to avoid the empty sheet error if needed...

If Not ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious) Is Nothing Then
    lstRw = ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row
End If

EDIT: I see I'm late again