CTRL+SHIFT+UP, then down Arrow

[Rowland Hamilton]

I'm working to consolidate multiple columns of data into two columns, one for the former column labels and one for the amounts of the data. In copying over the labels, even when CTRL-SHIFT-DOWN next to contiguous data on the left, I still end up pasting down to the bottom of the document. Solution was to paste up from the bottom with CTRL-SHIFT-UP, but I paste over the column heading that way. How can I add the additional arrow key down move in the VBA code (note: only a portion of code))?:

Columns("AC:AD").Select
 Selection.Insert Shift:=xlToRight
 Range("AC7").Select
 ActiveCell.Formula = "Item"
Range("AD7").Select
 ActiveCell.Formula = "Amount"
ActiveCell.Offset(0, 2).Select
 Selection.Copy
ActiveCell.Offset(0, -4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Thank you, Rowland

 

[Blade Hunter]

Change this:

Range(Selection, Selection.End(xlUp)).Select

To this:

Range(Selection, Selection.End(xlUp).offset(1,0)).Select

 

[Blade Hunter]

Second thoughts, don't select. Does this work?

Columns("AC:AD").Insert Shift:=xlToRight
Range("AC7").Formula = "Item"
Range("AD7").Formula = "Amount"
Range("AF7").Copy
Range(Range("AB7").End(xlDown).Offset(0, 1), Range("AB7").End(xlDown).Offset(1, 1).End(xlUp)).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Make sure you backup your data before running please.

 

[Rowland Hamilton]

Blade: Thanks. First one worked. I didn't try the second suggestion. I'm basic with VBA but what's the difference with the second? I'm assuming more efficient. less moves. - Rowland

(Aside: Blade Hunter - That sounds even cooler than Blade Runner and Blade, Vampire Hunter - I'm probably the first one to think of that just like you would be the first to Sing the theme to Rawhide - Rowland)

 

[Rowland Hamilton]

How can I Select everyting to the left until I run into the wall (aka column A) without knowing how many times I need to hit the left arrow to get past the spaces? ie:

Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select

Reason: I have to copy everything to the left of my new item and amount columns and paste them under the existing data set, which brings me that much closer to looping these commands about 19 times.

 

[mtharnden]

since this came up first while looking for something, even though its old, i wanted to post this here.

i like to avoid selects and wanted to do a simple ctrl shift right, ctrl shift down so i can clear a range. this gave me the idea on how to make that work in a single line without selecting anything like i had done in the past...

    Range(Range(Range("B11"), Range("B11").End(xlDown)), Range(Range("B11"), Range("B11").End(xlToRight))).ClearContents