Macro to insert a row above the last non blank row

Hi

I am using the following macro to copy a row of cells from one sheet to the next blank row on another sheet.
Is it possible to have the macro do almost the same but insert a row 1 row above the last non blank row?

"Sub Inv_1_InvList(): Dim w1 As Worksheet, w2 As Worksheet
Set w1 = Sheets("Inv 1"): Set w2 = Sheets("InvList")
w1.Range("AG5:EJ5").Copy _
w2.Range("A" & w2.Range("A" & Rows.Count).End(xlUp).Row + 1)
End Sub"

Thansk

Is that code working or is that your attempt to add a row? .Row + 1?

The reason I ask is that I get most of it apart from that bit. If that is your attempt to add a row try replacing it with .Rows.Insert

Does this work?

Please Login or Register  to view this content.

Sorry no. This adds a row to the first blank row and it inserts a row 2 rows above the row that was the last row before running the macro.

Hi xladapt,
No sorry, Your suggestion moves last cell in column A down but not the rest of the row.
It then pastes the copied row to 2 rows below the moved cell in column A.

Hi xladapt, This has inserted the whole row, but still pasted to 2 rows below, so I changed the +2 to -1 and this seems to work.

"w2.Range("A" & w2.Range("A" & Rows.Count).End(xlUp).Row + 2).PasteSpecial _
xlPasteValuesAndNumberFormats"

"w2.Range("A" & w2.Range("A" & Rows.Count).End(xlUp).Row - 1).PasteSpecial _
xlPasteValuesAndNumberFormats"

Many thanks for you assistance