Select all values above second to last row and copy and paste

Question

I am trying to work on a procedure to select the second to last row and all rows above it in a worksheet and copy and paste values.

I'm able to find the last row in my spreadsheet and select the entire row but I'm having a hard time offsetting the selected row. I'm getting an error related to the offset parameter.

'Removes formulas above the last line in the Auto Lease Data
Dim ALR As Long
Dim ALR2 As Long
With Sheets("Auto Lease Data")
   ALR = .Range("A" & .Rows.Count).End(xlUp).Row
   ALR2 = .Range("A" & ALR).EntireRow.Select
   .Range(ALR2).Offset(-1).Activate
End With

There is no need to `Select` before `Copy/Paste`. [See This](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Dec 14 '15 at 20:53

Scott Craner · Accepted Answer · 2015-12-14T20:22:57.577

First you are trying to use a number where a range is needed:

'Removes formulas above the last line in the Auto Lease Data
Dim ALR As Long
Dim ALR2 As Range
With Sheets("Auto Lease Data")
   ALR = .Range("A" & .Rows.Count).End(xlUp).Row
   Set ALR2 = .Range(ALR -1 & ":" & ALR -1)
   ALR2.select
End With

Now I assume you are going to try and get the whole range I would change to this:

'Removes formulas above the last line in the Auto Lease Data
Dim ALR As Long
Dim ALR2 As Range
With Sheets("Auto Lease Data")
   ALR = .Range("A" & .Rows.Count).End(xlUp).Row
   Set ALR2 = .Range("A1:J" & ALR -1)
   ALR2.select
End With

This will select everything from A1 to column J and down to the second to last line. Change J to the Last column. If you are going to copy and paste it will save time to define the last column.

If you want to copy and past the values back on itself(remove the formulas) then:

'Removes formulas above the last line in the Auto Lease Data
Dim ALR As Long
Dim ALR2 As Range
With Sheets("Auto Lease Data")
   ALR = .Range("A" & .Rows.Count).End(xlUp).Row
   Set ALR2 = .Range("A1:J" & ALR -1)
   ALR2.value = ALR2.value
End With

Hi Scott. Thank you for all that help. It unfortunately debugs on the last line, not too sure why. — UnbrokenChain, Dec 14 '15 at 20:19
@UnbrokenChain Yea my bad, that's what I get by editing here and not in VBE. See edit. — Scott Craner, Dec 14 '15 at 20:23
perfect, mission accomplished. Thank you for the help and especially for showing me that I was assigning a number to a variable instead of a range. — UnbrokenChain, Dec 14 '15 at 20:25

Select all values above second to last row and copy and paste

1 Answers1