Experts Exchange connects you with the people and services you need so you can get back to work.

VBA code to insert a blank row after Subtotal row in excel 2003

Here is part of my excel data: (the actual excel file has hundreds of rows )

CityID      Quantity
1                  3
1                  4
1                  4
Subtotal: 11
2                  3
2                  5
Subtotal: 8
Total:        19

I want to insert a blank row after each Subtotal row, format subtotal row in bold and size 11, total number is single underlined.
Last row Total row is bold , size 12, and number is double underlined.
the result is as below.

How do I write VBA code to do it? thanks,
Result.jpg

HemlockPrinters

Asked: 2013-01-05

Advertise Here

1 Solution

LVL 26

redmondbCommented: 2013-01-05

Hi, HemlockPrinters.

Edit: Minor change - ScreenUpdating turned off.

Please see attached. The code is...

Option Explicit

Sub Format_Totals()
Dim xCell As Range

Application.ScreenUpdating = False
    
    For Each xCell In Range("A1:A" & Range("A1").SpecialCells(xlLastCell).Row)
        If xCell = "Subtotal:" Then
            xCell.Offset(0, 1).Font.Underline = xlUnderlineStyleSingle
            With xCell.Resize(1, 2).Font
                .FontStyle = "Bold"
                .Size = 11
            End With
            xCell.Offset(1, 0).EntireRow.Insert
        ElseIf xCell = "Total:" Then
            With xCell.Resize(1, 2).Font
                .FontStyle = "Bold"
                .Size = 12
            End With
            xCell.Offset(0, 1).Font.Underline = xlUnderlineStyleDouble
        End If
    Next

Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian. Subtotal.xls

LVL 26

redmondbCommented: 2013-01-05

Thanks, HemlockPrinters.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

VBA code to insert a blank row after Subtotal row in excel 2003

Join & Write a Comment Already a member? Login.

Featured Post