Excel VBA - Insert a Row to a Specific Set of Columns

Question

Hi all,

I'm working on a spreadsheet to act as a master copy for some important information that will be kept track of for a project. On the main sheet I have a table for notes and a table for contacts set up seperated by several columns and some VB code to insert new notes or new contacts. However, when I insert a new note I want it to appear at the top of the table and to move all existing notes down a row. Is there any way to add a row to a specific set of columns so that any other data that is kept on cells with the same row, but in a different table, are not moved?

Answer 1

You'll have to code a VBA macro that selects all cells in the notes table as a Range object, and then moves them down one row.

I wouldn't know the exact code, but I'd advise you use the "Record Macro" function and do it by hand: select two cells in an Excel sheet, press the right mouse button and click "Insert". Then it'll ask you if you want to move the rows down. You can see in the newly recorded macro what the code is, and adjust it for your specific purpose. Good luck!

Answer 2

A basic example:

Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A2:D2")
rng.Insert Shift:=xlDown

...assuming that you want to insert 1 row of cells above the cells currently located in A2:D2.