Select Entire Last Row

Question

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.?

`Cells(Application.Rows.Count, 1).End(xlUp).EntireRow.Select` should start you off. Also no need to select stuff — MacroMarc, Jun 28 '17 at 21:48
To expand on @MacroMarc's last sentence, it's **highly** suggested to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/). — BruceWayne, Jun 28 '17 at 22:05
fwiw, if you are indeed looking from the bottom up to find the last used row, you do not have to .Insert 13 new rows before filling the next 13 blank rows with values/formulas. — , Jun 29 '17 at 01:15

score 3 · Accepted Answer · answered Jun 29 '17 at 00:08

3

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

answered Jun 29 '17 at 00:08

Nice Jeeped :). – Dan Donoghue Jun 29 '17 at 00:24

score 1 · Answer 2 · answered Jun 28 '17 at 22:16

1

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.

answered Jun 28 '17 at 22:16

Dan Donoghue

6,056
2
18
36

Select Entire Last Row

2 Answers2