Inserting Rows Without Selecting Anything?

Question

Hello SO,

I'm working in VBA and want to insert a row in a specific location without selecting it. The issue I'm having is that after the row is selected, the spreadsheet is scrolled down to that row when the script is finished running. I want to be able to do this without the spreadsheet being scrolled down to the inserted row.

Rows(i & ":" & i).Select
ActiveCell.EntireRow.Insert

I don't want to select A1 to get to the top.

Answer 1

Just do this:

Cells(i,1).EntireRow.Insert

Apply the action directly to the desired range, instead of selecting it first and applying the action to "Activecell".

A few additional notes:

• The "record a macro" feature generates code like your example, but I find that selecting the cell in advance is seldom necessary when writing your own code.
• A side benefit: operating on the cell directly instead of selecting it first can speed up your code by a factor of 10x to 100x !!

Answer 2

Rows(i & ":" & i).Insert will insert a row below without changing the selection.