Adding a new row in excel using VBA

I have a sheet which contains rows with the following text (D#):

  • D1
  • D2
  • D3

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?

Answers


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

Need Your Help

Transferring Variables Through a webpage

asp.net vb.net

I need to transfer a variable through a web page using asp.net. I know they are ways of doing this by using Session Variables but I do not want to use session variables, is there any way to do this