Posted by5 years ago
Archived
Simple VBA request - insert row below specific cell?
Select the worksheet tab which contains the formulas you want to auto fill, and right click to choose View Code from the context menu to go to the Microsoft Visual Basic for Applications window, and then copy and paste the following code into the Module: VBA code: Auto fill formula when inserting blank rows.
Hi
I need some help with some simple VBA.
So basically, lets say im logging letters that get sent. There are three columns as you can see in the workbook, Letter number, date, and version number.
Lets say a letter comes back to us and we need to edit it, and send it back. So I would need to go to the row that the letter is in, insert a new row underneath it, copy all of the data in the row before and paste it into the new row apart from the version number which will be changed to +1 what it was before. So if a letter has come back for a re work, and it was version 4 of that letter, i would go into my log, find the particular letter, and under the newest row would put the same row but with version 5 in the version column.
All I need, is a code that tells excel to insert a new row underneath the cell that i click on (so I would click on a cell in column A, say Letter number 8 for example), then run the macro that copies the row I click on and pastes it into a new row underneath it, but changes the version number to +1 what it was before.
All i really need, is the bit of code that tells excel where to insert the row, so this would be directly underneath the selected cell at the time of pressing the macro key, the rest I have by using macro record.
Thanks EJ
81% Upvoted
If you only want to add rows to the table without actually inserting rows across the worksheet then you need to use a loop to add multiple single rows. Is this what you want to do? or do you want to insert rows across the worksheet as per Rick's answer?
Try the code below to add rows to the table only without inserting rows across the entire worksheet.
Note that the code expands the table on the worksheet; it does not insert rows across the entire worksheet. However, it inserts rows within the table range and any data below the table moves down.
As a matter of interest, if you activate the code recorder and select multiple rows of the table and Right Click -> Insert -> Table rows above then you will find that it has recorded multiple lines of code by adding only one row for each line of code.
Sub AddRowsToTable()
Dim i As Long
Dim CYA_rows As Long
CYA_rows = 5
For i = 1 To Rows('3:' & 2 + CYA_rows).Rows.Count
'Following line Adds a single row at row 3 of the table
Range('Table1').ListObject.ListRows.Add (3)
Next i
Dim CYA_rows As Long
CYA_rows = 5
For i = 1 To Rows('3:' & 2 + CYA_rows).Rows.Count
'Following line Adds a single row at row 3 of the table
Range('Table1').ListObject.ListRows.Add (3)
Next i
End Sub
Hoping one of our answers helps you.
Regards, OssieMac