Streamlining macro created by macro recorder

[Luke]

I used the macro recorder in Excel to create a nice macro to reorganize some
raw data into a format more useful for Excel to work with. In moving the
data around, at one point I selected a range (J8:R8) and copied/pasted the
formulas in it down to the bottom of the worksheet, then copied that entire
range and pasted values. That portion of the code (from the recorder) looked
like this:

Range("J8:R8").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks :=False, Transpose:=False

I'd really like to knock this down to just a couple of lines, maybe three at
most. Can anyone help out? Thanks.

 

[Matthew Herbert]

Luke,

Your "three" line code is below, along with a separate approach. (You'll
likely get a number of answers that all have a slightly different way to
achieve your results).

Best,

Matthew Herbert

Sub ThreeLines()
Range("J8:R8").Copy Range(Range("J8:R8"), Range("J8:R8").End(xlDown))
Range(Range("J8:R8"), Range("J8:R8").End(xlDown)).Copy
Range(Range("J8:R8"), Range("J8:R8").End(xlDown)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Sub SeparateApproach()
Dim Rng As Range
Set Rng = Range("J8:R8")
Set Rng = Range(Rng, Rng.End(xlDown))
Rng.FillDown
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Patrick Molloy]

Range("J8:R8").Copy
With Range(Selection, Selection.End(xlDown))
.PasteSpecial xlPasteAll
.Value = .Value
End With
Application.CutCopyMode = False

 

[Luke]

Thanks. That pretty much did it. The only exception was on the first line
after the Sub. Instead of "Copy Range(Range("J8:R8"), ..." I ended up with
"Copy Range("J8:R8", ...". Other than that, it was the same.

Thanks, again.