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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.