I want to locate the last used row and then copy the row that is 2 before the last one. Any ideas?
Thanks
Gary
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
[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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
I've been told there was an error with the link "this article" in my last post. My apologies, it's fixed now
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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