VBA Excel - Insert a partial row in active cell

Question

I have been experimenting with VBA code and wanted to figure out how to insert a partial row where there is an active cell (Range from B:E).

A beginner here, will appreciate the input

Thanks!

Sub Adding()
'
' Adding Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Range("B177:E177").Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=-162
    Range("K9").Select
    Selection.AutoFill Destination:=Range("K9:K1936"), Type:=xlFillDefault
    Range("K9:K1936").Select
    ActiveWindow.ScrollRow = 1904
    ActiveWindow.ScrollRow = 1902
    ActiveWindow.ScrollRow = 1893
    Range("L177").Select
    ActiveCell.FormulaR1C1 = _
        "Test. "
End Sub

so you want to insert 4 cells (shifting the current ones down) from columns B:E in the row of the activecell? (also please keep questions to 1 actual question. you have two here — Scott Holtzman, Aug 01 '18 at 21:55
@ScottHoltzman Yes, that is correct. I also removed the second question sorry about that. — Jaan, Aug 01 '18 at 22:18

Profex · Accepted Answer · 2018-08-02T14:00:46.990

Since you are staring out, you might want to read How to avoid using Select in Excel VBA

Also, the only VBA that is required to insert a few cells is

ActiveSheet.Range("B177:E177").Insert Shift:=xlDown

...the rest of the code is just fluff & tinkering and doesn't have to do with the question. Recording Macros is a great way to quickly get pointed in the right direction and getting exposed to new properties/functions; but once you get what you need, remove the extra fluff (anything that says .Select/Selection/ActiveWindow) which just slows the code down anyway.

There is a difference between Activecell and Selection.

Selection is a Range variable of all the cells that are selected/highlighted.
ActiveCell is a Range that contains only 1 cell. The cell that is highlighted white and if you start typing the value will change. You can change the active cell to the {previous}/next cell in a selection by hitting the {shift+} Tab key.

So to insert the cells that are selected, use:

Selection.Insert Shift:=xlDown

for just the active cell, use:

ActiveCell.Insert Shift:=xlDown

Note: Selection/ActiveCell are always based on the ActiveworkBook.Activeworksheet.

Thank you for the response. I have made that change, but it looks like it didn't make the proper action that I wanted. I was looking to have this done based on the active cell, so whichever cell I have currently clicked. — Jaan, Aug 02 '18 at 13:43

VBA Excel - Insert a partial row in active cell

1 Answers1