Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining macro created by macro recorder
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining macro created by macro recorder
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 "Luke" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining macro created by macro recorder
Range("J8:R8").Copy With Range(Selection, Selection.End(xlDown)) .PasteSpecial xlPasteAll .Value = .Value End With Application.CutCopyMode = False "Luke" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining macro created by macro recorder
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. "Matthew Herbert" wrote: 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 "Luke" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Hiding New Workbook created using Macro Recorder | Excel Programming | |||
Macro recorder | Excel Discussion (Misc queries) | |||
Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming |