Excel Macro Row increment

Question

I am trying to copy a range of cells in a single column and transpose them above as a row. The problem I am having is to try to increase the Rows(45:45) where each row is being copied, otherwise all of the rows get overwritten. When I run the macro I get a syntax error on Destination:= Range line

Range("A53:A63").Copy
Range("A52").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=True
Range("A53:A63").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Rows("52:52").Cut
Rows("45:45").Select
Destination:=Range("A" & Rows.Count).End(xlDown).Offset(1, 0)
Selection.Insert

With Rows.Count you are starting at the bottom so there is no where to go with the xlDown. Change `End(xlDown)` to `End(xlUp)` — Scott Craner, Dec 30 '16 at 22:41
Also, I highly suggest you [avoid using `.Select/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), as it can cause unforseen issues, but can also greatly shorten and tighten up your code. — BruceWayne, Dec 30 '16 at 22:43
@ScottCraner - Even with your suggestion, I still get a syntax error on the Destination line — Tori, Dec 30 '16 at 22:47
`Destination:=` is not a command, it's a named parameter to some other command. — YowE3K, Dec 30 '16 at 22:53
Try just `Rows("52:52").Cut Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0)` ...there's no reason to select row 45 either, as far as I can tell. I think this code, and your understanding, will benefit greatly from reading through the link I posted above. (It did for me anyways). — BruceWayne, Dec 30 '16 at 22:53
(or even just `Rows("52:52").Cut Range("A" & Rows.Count).End(xlUp).Offset(1, 0)`) — BruceWayne, Dec 30 '16 at 22:58
What I am trying to do is cut from row 52 and insert the row the fist time at row 45, the second at 46, etc.. Thanks for the link. — Tori, Dec 30 '16 at 23:18
Then why not just cut out the lines between 45 and 51 which will effectively do that. `Rows(45:51).Delete` it will shift row 52 to 45 and 53 to 46 and so on. — Scott Craner, Dec 30 '16 at 23:24
If you transpose the range A53:A63 the way you do it, you might get unexpected results in case any of the cells in the range contain formulas with references that are not absolute. — M.Doerner, Dec 31 '16 at 00:19
@M.Doerner, the range that does not contain any formulas, only numbers and text — Tori, Dec 31 '16 at 01:37

score 0 · Answer 1 · answered Jan 01 '17 at 09:43

Although I do not really understand what you are trying to do but maybe this helps.

Sub test()
Range("A53:A63").Copy
Range("A52").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=True
Range("A53:A63").Select
Application.CutCopyMode = False
Selection.ClearContents
Rows("52:52").Copy
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Excel Macro Row increment

1 Answers1