Adding a new row in excel using VBA
I have a sheet which contains rows with the following text (D#):
I need to add a new row "D4" (appending the next integer to the letter D) under the last entry which is "D3". Of course,next time the new entry would be "D5" etc... How can I do that using VBA script or macro?
In the Column D enter the following Worksheet Formula and extend it to entire region:
="D" & ROW()
You can automate it via Excel VBA macro as shown in the following code snippet which populates Range("D1:D20") with Worksheet Formula:
Range("D1:D20").Formula = "= ""D"" & Row()"
For your particular task, you may consider using the Macro recorder and then "polish" the VBA code.
Hope this will help.
Use this to find the last row on your sheet:
ColNum = 4 LastRow = ActiveSheet.Cells(Rows.Count, ColNum).End(xlUp).Offset(Abs(ActiveSheet.Cells(Rows.Count, ColNum).End(xlUp).Value <> ""), 0).Row
The value of LastRow will be the next open row in your sheet.
To reference the cell in code use:
ActiveSheet.Range("D" & Trim(Str(LastRow))).Value = "YourValue"
or with indexes:
ActiveSheet.Range(Cells(LastRow, 4), Cells(LastRow, 4)).Value = "YourValue"
this fills next cell in the given column of a given worksheet with a value whose left digit part is incremented by one
Sub FillRow(ws As Worksheet, nCol As Long) Dim lastRow As Long With ws lastRow = .Cells(.Rows.Count, nCol).End(xlUp).row With .Cells(lastRow, nCol) .Offset(1).Value = "D" & Right(.Value, Len(.Value) - 1) + 1 End With End With End Sub