Microsoft Excel —

Fill Outline View

The report is almost complete. You are nearly a Data, Subtotals command away from having everything you need. Before you can use the Subtotals command, however, you need to fill in all the blank cells in the outline view of column A.

Fixing the Outline view requires just a few obscure steps. Here are the steps in the user interface:

1.
Select all the cells in column A that make up the report.
2.
Select Edit, GoTo to bring up the GoTo dialog box. Click the Special button to bring up the GoTo Special dialog box. Select Blanks to select only the blank cells.
3.
Enter an R1C1-style formula to fill the blank with the cell above it. This formula is =R[1]C. In the user interface you would type an equals sign, press the up-arrow key, and then press Ctrl+Enter.
4.
Reselect all the cells in column A that make up the report. This is necessary because the Paste Special step cannot work with noncontiguous selections.
5.
Copy the formulas in column A and convert them to values by using the Values option in the Paste Special dialog box.

Fixing the Outline view in VBA requires fewer steps. The equivalent VBA logic is shown here:

1.
Find the last row of the report.
2.
Enter the formula =R[-1]C in the blank cells in A.
3.
Change those formulas to values.

The code to do this follows:

Dim FinalReportRow as Long
    ' Fill in the Outline view in column A
    ' Look for last row in column B since many rows
    ' in column A are blank
FinalReportRow = WSR.Range("B65536").End(xlUp).Row
With Range("A3").Resize(FinalReportRow - 2, 1)
    With .SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
    End With
    .Value = .Value
End With


Final Formatting

The last steps for the report involve some basic formatting tasks and then adding the sub totals. You can bold and right-justify the headings in row 3. Set rows 13 up so that the top three rows print on each page:

' Do some basic formatting
' Autofit columns, bold the headings, right-align
Selection.Columns.AutoFit
Range("A3").EntireRow.Font.Bold = True
Range("A3").EntireRow.HorizontalAlignment = xlRight
Range("A3:B3").HorizontalAlignment = xlLeft

' Repeat rows 1-3 at the top of each page
WSR.PageSetup.PrintTitleRows = "$1:$3"