CODEWITHSUNDEEP
excelvbacountrowscopy-paste
ladymrt
ladymrt

Reputation: 95

Select Entire Last Row

I am trying to get my code to find the last row in a sheet and copy THE WHOLE ROW and paste it below. Currently I have: 

Cells(Application.Rows.Count, 1).End(xlUp).Select
Selection.Copy
Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.Insert Shift:=xlDown
 Application.CutCopyMode = False

Which is great at finding the last cell in the row, however when it copies/pastes this, it's only grabbing that first cell. SO, what I'm wondering is if you can use this code to find the last row and then SELECT the ENTIRE row, and then copy/paste it below. Keep in mind I can't just type 

Rows("3:3").Select
Selection.Copy
Rows("4:4").Select
Selection.Insert Shift:=xlDown

Because the row will be variable. Thanks!

Dim lastRow As Long

lastRow = Cells(Application.Rows.Count, 1).End(xlUp).Row

 Rows(lastRow).Select
 Selection.Copy
 Rows(lastRow).Offset(1, 0).Select
 Selection.Insert Shift:=xlDown
 Application.CutCopyMode = False

The code above will find the last row, select it, copy it, and paste it below. However, I need to copy the last row and paste it in several rows below it in some cases, so:

  Rows("3:3").Select
Selection.Copy
Rows("4:16").Select
Selection.Insert Shift:=xlDown

Something to that effect, however I'm not able to paste the selected row in several rows because

 Rows(lastRow:4).Select

freaks it out. Is there any way to add that "16" to the last row so it copies the row and pastes it into the next 14 rows, etc.?

Upvotes: 1

Views: 2252

Answers (2)

user4039065
user4039065

Reputation:

You could 'select' the multiple rows with a .Resize then Fill Down.

dim rws as long
rws = 14   'total # of rows 3:16
with worksheets("sheet1")
    .cells(.rows.count, "A").end(xlup).resize(rws, .columns.count).filldown
end with

Upvotes: 3

Dan Donoghue
Dan Donoghue

Reputation: 6206

You need to resize it

Sub temp()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Rows(lr).Copy
    Range("A" & lr).Resize(14, Columns.Count).PasteSpecial xlPasteAll
End Sub

Don't select things, it's very bad practice.

Upvotes: 1

Related Questions