cellMatrix.net
Spreadsheet Modeling and Related Topics

About

Formulas, Charts, and Models Created with Microsoft Excel.
More . . .

Statistics

  • Total Entries - 102
  • Current Viewers - 17

Categories

Recent Comments

Syndicate

Validate

Pivot Table Fill

By their nature pivot tables do not show rows that repeat. The pivot table shows the first unique row descriptor and then repeats blank cells until the next unique descriptor is shown. You might need to show all descriptor values - for example if you want to load the results of a pivot table into an ACCESS database.

If you first copy the pivot table as values, you can then apply the macro below to the descriptor range. The macro loops through the descriptor range and fills the empty cells with the descriptor values.

Sub PivotTableFill()
    Dim Rng As Range
    For Each Rng In Range("A1", "C100")
        If IsEmpty(Rng) = True Then
            Rng.Value = Rng.Offset(-1, 0).Value
        End If
    Next Rng
End Sub



Comment Form

Name: (Required)
E-Mail Address: (Optional)
Location: (Optional)
Web Site Address: (Optional)

Remember my personal information?
Notify me of follow-up comments?

Before submitting your comment, please enter the phrase you see below: