Formatting of pasted row via VBA

Hi all

I have the current code:

Sub addallrows()

Dim divrow As Long
Dim arearow As Long
Dim notoppd As Long

For divrow = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(divrow, "E") <> Cells(divrow - 1, "E") Then
        Cells(divrow, "E").EntireRow.Insert
        Cells(divrow, "A").Value = Cells(divrow + 1, "E").Value
End If

Next divrow

For arearow = Cells(Rows.Count, "G").End(xlUp).Row To 3 Step -1
If Cells(arearow, "G") <> Cells(arearow - 1, "G") Then
        Cells(arearow, "G").EntireRow.Insert
        Cells(arearow, "A").Value = Cells(arearow + 1, "G").Value
End If

Next arearow

For notoppd = Cells(Rows.Count, "E").End(xlUp).Row To 3 Step -1
If Cells(notoppd, "E") <> "Adults/Phys Dis/Older People" Then
        Cells(notoppd, "H").Value = Cells(notoppd, "F").Value
        
End If

Next notoppd

Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select

End Sub

Where rows are inserted, can I stop them from copying the formatting from the row above??

Also, I would like the text that is pasted into the cell A (for divrow and arearow) to be left aligned and bold.

Can someone help?

Try this:

Sub addallrows()

Dim divrow As Long
Dim arearow As Long
Dim notoppd As Long

For divrow = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(divrow, "E") <> Cells(divrow - 1, "E") Then
        Cells(divrow, "E").EntireRow.Insert
        With Cells(divrow, "A")
            .EntireRow.ClearFormats
            .Value = Cells(divrow + 1, "E").Value
            .Font.Bold = True
            .HorizontalAlignment = xlHAlignLeft
        End With
End If

Next divrow

For arearow = Cells(Rows.Count, "G").End(xlUp).Row To 3 Step -1
If Cells(arearow, "G") <> Cells(arearow - 1, "G") Then
        Cells(arearow, "G").EntireRow.Insert
        With Cells(arearow, "A")
            .EntireRow.ClearFormats
            .Value = Cells(arearow + 1, "E").Value
            .Font.Bold = True
            .HorizontalAlignment = xlHAlignLeft
        End With
End If

Next arearow

For notoppd = Cells(Rows.Count, "E").End(xlUp).Row To 3 Step -1
If Cells(notoppd, "E") <> "Adults/Phys Dis/Older People" Then
        Cells(notoppd, "H").Value = Cells(notoppd, "F").Value
        
End If

As you may know, the format of a cell contains many attributes. Look at the Format->Cells... Excel menu option. When you say that you don't want to copy the format, what do you mean? Do you want to make the format something like another row on the spreadsheet? If that is the case, then you could copy that row to the clipboard and then do a PasteSpecial for format only onto the new row.

Let us know if you need more help.

The clearformats should remove wraptext anyway. Can you post a sample workbook showing the problem?

Your code never processes rows 1 or 2 though?

Glad it's sorted.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save