Converting Pivot Table to Table

[shortpants13]

I sometimes need to convert a pivot table to a standard data table for vlookups, etc..


When I do this, row headings with multiple child row headers are merged cells covering the range of child rows. Is there a way to un-merge the parent row header and copy the value to all of the child rows?


Thanks.

 

[Luke M]

Is it really a merged cell, or just a cell with blanks underneath? Either way, this is what you could do:


Select all the cells of concern

Format cells, Alignment, unmerge cells (if needed)

Ctrl+g to bring up GoTo dialogue

Special, blank cells.

Hit the = symbol, then hit the up arrow, then hold Ctrl and hit Enter.

All the blank cells should now be filled in.


If needed, you can select column, and do an in-place copy - paste values.

 

[shortpants13]

Works like a charm! I knew there had to be an easier way.


Thanks,

Brett

 

[JesseH77]

I do this all the time and wrote a macro to do it for me. Takes the selection and fills every blank cell with the cell above it.


Sub FillBlankLines()


ScreenUpdating = False

Dim rngSelection As Range

Dim rngCell As Range


Set rngSelection = Selection


For Each rngCell In rngSelection

If rngCell.Value = "" And rngCell.Row <> 1 Then rngCell.Value = rngCell.Offset(-1, 0).Value

Next


ScreenUpdating = True

End Sub