Solved: Find Last Row

[zoom38]

I want to locate the last used row and then copy the row that is 2 before the last one. Any ideas?

Thanks
Gary

[acw]

Gary

Is there a particular column that will always have an entry and can be used to test for the row? Or will the column always vary?


Tony

[mdmackillop]

[vba]
Dim LastRow As Long
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Rows(LastRow - 2).Copy

[/vba]
or, omitting variables

[VBA]
Rows(Cells.SpecialCells(xlCellTypeLastCell).Row - 2).Copy

[/VBA]

[zoom38]

ACW column A has data in it.
MDMack after a while of troubleshooting you're coding works. My problem was that there was cell borders down around row 3000 which I wasnt aware of. Apparently the styles setting on my work network excel includes borders.

Thanks
Gary

[johnske]

Hi zoom38,

Unfortunately SpecialCells(xlCellTypeLastCell) refers to the last cell in the used range and the used range includes all values, formulas, formatting etc. so it's not always reliable if you want to find the last row with a value in it (that's why you had difficulties).

As acw was getting at, if you have a row (say A) that always has a value in it, you can use

[vba]Rows(Range("A" & Rows.Count).End(xlUp).Row - 2).Copy[/vba]

It also depends on what you want to consider to be the last row (it's usually taken to be simply the last row with any sort of "values" in it - but you may sometimes want to find the last formula row, or the last row with a number, or the last row with text...etc.).

For more on finding the last row (or column), you could read this article

HTH

[johnske]

I've been told there was an error with the link "this article" in my last post. My apologies, it's fixed now

[zoom38]

HTH fantastic article, has all the info I need. My first two worksheets have text in the last row and the next 13 have formulas in the last row.
Thanks
Gary