Excel VBA button which adds a new row and copy formual from the row above

Question

I am completely new to macros/VBA. All I am trying to do is to create a button which should add new row. I also want to copy formulas to the new row from the row above. My start column is B.

this is a code i copied from somewhere

 Private Sub Button1_Click(Optional line As Integer = -1)
    Dim target As Range
    Dim cell As Range
    Dim rowNr As Integer

    Set target = Range("B5:BH323")

    If line <> -1 Then
        rowNr = line
    Else
        rowNr = target.Rows.Count
    End If

    target.Rows(rowNr + 1).Insert
    target.Rows(rowNr).Copy target.Rows(rowNr + 1)
    For Each cell In target.Rows(rowNr + 1).Cells
        If Left(cell.Formula, 1) <> "=" Then cell.Clear
    Next cell

End Sub

this code works well. It inserts a new row with the formulas but in this code insert happens only below the range mentioned in the code, I want the insert to happen after the last row with data. Can some one correct this code please, also the newly inserted doesn't copy the borders.

What I need is is the line which specifies the last row with data and also makes sure that the border format is copied as well

dgorti dgorti · Accepted Answer · 2016-08-17T04:56:30

Use Cells(Rows.Count, "B").End(xlUp).Row to find the last row.

Excel VBA button which adds a new row and copy formual from the row above

1 Answers