excelvba

How to Define the row after Lastrow in VBA


I simply need to change the font of a row after the table last row + 2rows for cells from ("A: M") but I don't know how to write that in VBA code ... I have wrote the last section in the below code but unfortunately wrong

any sugesstion please

Sub Addnewrow()

Dim lo As ListObject
    Dim newRow As ListRow
    Dim cpyRng As Range
    
    Set lo = Range("Data").ListObject
    Set newRow = lo.ListRows.Add
    
With newRow
.Range(12).FillDown

 With Range(LastRow + 2)
 
        .Font.Size = 12
        .Font.Name = "Tahoma"
        .Font.Bold = True
    End With

End Sub

Solution

  • Rather than reference the table using "whatever listobject is covering this range" (Set lo = Range("Data").ListObject) refer to the table by name which can be found in the Table Design tab that appears when you select a cell within the table.

    enter image description here

    I'm not sure what you're doing with the .Range(12).FillDown. If a table contains a formula it should automatically copy it down when you add a new row.

    The part of the table where the data is held is called the DataBodyRange so you can find the end of that and then offset by two more rows to get two rows beneath the table.

    Public Sub AddNewRow()
    
        'Reference the table by name rather than location.
        'You can then move the table if needed without breaking the code.
        Dim lo As ListObject
        Set lo = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
        
        'Add a new row to the table and add some data to the 12th column in the new row.
        With lo.ListRows.Add
            .Range(12).Value = "ABC"
        End With
        
        'Format 2 rows beneath the table.
        With lo.DataBodyRange
            With .Rows(.Rows.Count).Offset(2)
                .Font.Size = 12
                .Font.Name = "Tahoma"
                .Font.Bold = True
            End With
        End With
    
    End Sub