If cell is blank, fill with text above

[XLerator]

Hi,

I think this is an easy one...

After doing my pivot table, for some reason, it doesn't fill down all of the rows with the data in the first column---so I have to manually fill all of the blank columns with the text.

Is there a macro I can use in which it finds a blank cell in the active area, and then it fills with whatever is directly above it....i want it to stop of course, otherwise it wouldn't end...but it would have to end when the active area ends---

any ideas?

Dylan

[eliW]

On 2002-05-23 22:35, XLerator wrote:
Hi,

I think this is an easy one...

After doing my pivot table, for some reason, it doesn't fill down all of the rows with the data in the first column---so I have to manually fill all of the blank columns with the text.

Is there a macro I can use in which it finds a blank cell in the active area, and then it fills with whatever is directly above it....i want it to stop of course, otherwise it wouldn't end...but it would have to end when the active area ends---

any ideas?

Dylan

Hi Dylan,
You don't need a macro here!

1) from one of the cells in your range type: CTRL+Shift+8 (this selects your entire range)
2) fron ToolBar press: Edit | GoTo | Special | Blanks and OK (your blanks are selected)
3) now type in the active cell: =[and point to the cell above], (eg. if your active cell is say H3 then enter "=H2")
4) press CTRL+Enter simultaniously

All your blanks will be filled with the content above them.

Eli

[ This Message was edited by: eliW on 2002-05-24 00:19 ]

[HumanJHawkins]

That's a pretty manual solution for a big pivot with loads of rows. I used the following macro as a base and tweaked it for my specific needs. If you've got a lot of data, you may find this much better:
http://www.ozgrid.com/Excel/excel-fill-blank-cells.htm