PDA

View Full Version : Solved: Find Last Row



zoom38
05-21-2006, 07:00 PM
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
05-21-2006, 11:04 PM
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
05-22-2006, 12:45 AM
Dim LastRow As Long
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Rows(LastRow - 2).Copy


or, omitting variables


Rows(Cells.SpecialCells(xlCellTypeLastCell).Row - 2).Copy

zoom38
05-22-2006, 09:22 PM
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
05-22-2006, 10:00 PM
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

Rows(Range("A" & Rows.Count).End(xlUp).Row - 2).Copy

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 (http://xlvba.3.forumer.com/index.php?showtopic=23)

HTH

johnske
05-23-2006, 02:13 AM
I've been told there was an error with the link "this article (http://xlvba.3.forumer.com/index.php?showtopic=23)" in my last post. My apologies, it's fixed now :)

zoom38
05-23-2006, 05:52 AM
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