How to Define the row after Lastrow in VBA

[Ramadan]

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 a code to Addnewrow and I 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("A" & LastRow + 2)
   .Font.Size = 12
   .Font.Name = "Tahoma"
   .Font.Bold = True
End With
End With
 
End Sub

Cross-posted at How to Define the row after Lastrow in VBA

 

[DanteAmor]

I simply need to change the font of a row after the table last row + 2rows for cells from ("A: M")

You mean that after the table you want to apply the format, for example:

 

[Ramadan]

You mean that after the table you want to apply the format, for example:

You are right it's like that, it seems silly but unfortuantely I also have a strange issue forced me to ask for that, as every time I add a new row the font size of this total row under the table change itself to regular 11 instead of Tahoma 12 - and I tried everything but nothing so I thought to add a rule to the code to resize it back​

 

[Alex Blakenburg]

In Dante's image there are 2 blank rows but LastRow + 2 I would have thought only leaves one blank row.
Change the offset(2) to offset(3) if you want 2 blank rows.

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.Offset(2)           ' This leaves 1 blank line
            .Font.Size = 12
            .Font.Name = "Tahoma"
            .Font.Bold = True
       End With
    End With
    
End Sub

Note:
• When I run the Add new row line it pushes the formatted cells down automatically, so that the font settings are still with the same data. We would need to see what is different about your data if you wanted us to trouble shoot why that is not happening for you.
• Also if you have a formula in Column 12 it should automatically fill down to the new row. If it is not doing that we can tell you how to reset the column so that it does automatically fill down.

 

[Ramadan]

In Dante's image there are 2 blank rows but LastRow + 2 I would have thought only leaves one blank row.
Change the offset(2) to offset(3) if you want 2 blank rows.

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.Offset(2)           ' This leaves 1 blank line
            .Font.Size = 12
            .Font.Name = "Tahoma"
            .Font.Bold = True
       End With
    End With
 
End Sub

Note:
• When I run the Add new row line it pushes the formatted cells down automatically, so that the font settings are still with the same data. We would need to see what is different about your data if you wanted us to trouble shoot why that is not happening for you.
• Also if you have a formula in Column 12 it should automatically fill down to the new row. If it is not doing that we can tell you how to reset the column so that it does automatically fill down.

Thank you so much for your continous help - I actually don't know what's wrong in my sheet to affect the font setting, however it only affects on two cells in the total row and these to cells have countif formula to collect totals from table while other cells with text in the same row still the same no change.. for Colunm 12 it has a text not a formula that's why I thought to put it as filldown- if you like i can share a link for my file to test and thank you anyway for the help

 

[DanteAmor]

In Dante's image

Hi Alex, I hope you have a great start to the year.

Receive a cordial hug.

 

[Palo]

Maybe this will help:

Sub find_end()



'This code will find the first empty cell in column A there must be something in all columns before last filled cell in column A

ActiveSheet.Range("A:A").find("").Select

'Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(0, 13)).Select says how many rows to select (1,0)means 2 (2,0) means 3 etc and (0,13)
'determines how many columns should be selected

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(0, 13)).Select

With Selection.Font
.Name = "Tahoma"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

'the code below says which cell should be selected after formating cells (delete REM )
Rem Range("A1").Select

End Sub

 

[Alex Blakenburg]

if you like i can share a link for my file to test and thank you anyway for the help

If you want to put a file for us to look at on a sharing platform such as Dropbox, Google Drive, OneDrive etc, I am happy to take a look.
You will need to make the it available to anyone with the link and post the link here. As such you will want to take out any sensitive information. Just make sure that any example file still experiences the issue we are trying to resolve.

Re: Fill Down of Text
Its not that common to have the same text repeating on every row but I have done it as part of converting data. If you really want the same text on every row and have that automatically picked up on new rows then:
• Delete all the current data in that column
• In one of the cells make your text into a formula by typing in
="The Text to repeat"
If you have cleared out all cells in that column the formula will fill every cell and will automatically appear on new rows.

 

[Ramadan]

If you want to put a file for us to look at on a sharing platform such as Dropbox, Google Drive, OneDrive etc, I am happy to take a look.
You will need to make the it available to anyone with the link and post the link here. As such you will want to take out any sensitive information. Just make sure that any example file still experiences the issue we are trying to resolve.

Re: Fill Down of Text
Its not that common to have the same text repeating on every row but I have done it as part of converting data. If you really want the same text on every row and have that automatically picked up on new rows then:
• Delete all the current data in that column
• In one of the cells make your text into a formula by typing in
="The Text to repeat"
If you have cleared out all cells in that column the formula will fill every cell and will automatically appear on new rows.

Hi Alex, I would like to thank you so much for your interest to help and with pleasure I will share my file with you to test - regarding column 12 text filldown actually it's not the same text in all the column cells but it's subject to change depending on the update after the first data entry. so I need to fill down only the last row text when adding a new row because it has to be the same of last row first time created then it might be changed later on as I said.. so the column doesn't has one fixed text string

here is the link of my file on google drive with the addnewrow macro before adding your suggested reformatting part

https://docs.google.com/spreadsheets/d/1xK1qIgE7-mPTmssmCk1c8LyKvrBfO2_y/edit?gid=1822075109#gid=1822075109

 

[Alex Blakenburg]

I'm afraid it is asking me for permissions which means it has not been set to be available to anyone with the link.