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 separated 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?
Add a comment
|
2 Answers
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.
2 Comments
bottbott
I'm going to attempt to implement this code today, but I had forgot to mention in my initial question if there would be any implications for having merged cells in the table. For example, one table above the notes table has several cells in it that are unmerged whereas the same cells in the notes table are merged. Would there be any issues due to that?
Helen Toomik
Well, if you are going to insert cells in the middle of a merged area then the inserted cells may end up merged, too. And if row 2 has merged cells, and you insert a row above them, then the inserted row isn't going to magically be merged. So basically you have to test it with your actual sheet and see what the outcome is.
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!
