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
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.
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