excel - How to Define the row after Lastrow in VBA - Stack Overflow

admin管理员组

文章数量:1125619

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

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

• excel
• vba

Share Improve this question Follow edited 2 days ago Mayukh Bhattacharya 27k88 gold badges2929 silver badges4242 bronze badges asked 2 days ago Ramadan MoussaRamadan Moussa 6577 bronze badges 3

• 1 Providing LastRow contains a numeric value then Range(LastRow + 2) will equate to something like Range(5), so you're trying to write Range(5).Font.Bold = TRUE which will return a Method 'Range' of object '_Global' failed error. Using Rows(LastRow + 2) would work - it will apply to the whole row on whichever sheet is currently active though. – Darren Bartrup-Cook Commented 2 days ago
• 2 You may check the Rows property of a Range/Worksheet (learn.microsoft.com/en-us/office/vba/api/excel.worksheet.rows) – Shrotter Commented 2 days ago
• @DarrenBartrup-Cook can you please read my question again after edit and tel me where can I add this line please – Ramadan Moussa Commented 2 days ago

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

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

I am not sure what you are trying to achieve, but adds a row to table and then formats the cells from A to M below the table.

Dim lo As ListObject
Dim newRow As ListRow
Dim cpyRng As Range

'Add Dim lastRow
Dim lastRow As Long

Set lo = Range("Data").ListObject
Set newRow = lo.ListRows.Add

'lastRow = table's forst row + rowcount
lastRow = lo.Range.Row + lo.ListRows.Count

newRow.Range(lastRow).FillDown

With Range("A" & lastRow + 1 & ":M" & lastRow + 1)
    .Font.Size = 12
    .Font.Name = "Tahoma"
    .Font.Bold = True
End With

Do not use nested With-clause. Also there is a missing End With in your code.

You need to specify full cell reference. LastRow only indicating row number but not cell. Try-

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

Replace column A with your actual column.

本文标签：