fill in blank cells after Grouping Subtotal

**sj2487** · 08-31-2009, 12:29 PM

I just did a Group Subtotal. How do I fill all the blank cells in a column with the information in the cell above it without going to every individual cell?

**JBeaucaire** · 08-31-2009, 02:08 PM

1) Select the full range of cells with blanks
2) Press F5 and click on Special
3) Select Blanks and click on OK
4) Press the equal key: =
5) Press the Up Arrow once
6) Press CTRL-ENTER

You have the values but they are formulas. To remove the formulas
7) Select the full range again
8) Press CTRL-C to copy
9) Select Edit > Paste Special > Values

**Ron Coderre** · 08-31-2009, 02:11 PM

Here's one approach...

With A2:A10 containing a mix of text and blanks
Try this:

Please Login or Register  to view this content.

While only the blank cells are selected:

Please Login or Register  to view this content.

Now each (formerly) blank cell displays that previous value from above.

To hardcode them:

Please Login or Register  to view this content.

Does that help?

**sj2487** · 08-31-2009, 02:40 PM

Originally Posted by JBeaucaire

1) Select the full range of cells with blanks
2) Press F5 and click on Special
3) Select Blanks and click on OK
4) Press the equal key: =
5) Press the Up Arrow once
6) Press CTRL-ENTER

You have the values but they are formulas. To remove the formulas
7) Select the full range again
8) Press CTRL-C to copy
9) Select Edit > Paste Special > Values

thank you but when I push the arrow up key it puts the cell above the first cell ie c25 into all of the blank cells not the cell relative to the each blank cell. Is there a way to do that?
Thanks
Stan

**sj2487** · 08-31-2009, 02:41 PM

Originally Posted by Ron Coderre

Here's one approach...

With A2:A10 containing a mix of text and blanks
Try this:

Please Login or Register  to view this content.

While only the blank cells are selected:

Please Login or Register  to view this content.

Now each (formerly) blank cell displays that previous value from above.

To hardcode them:

Please Login or Register  to view this content.

Does that help?

thank you but when I push the arrow up key it puts the cell above the first cell ie c25 into all of the blank cells not the cell relative to the each blank cell. Is there a way to do that?
Thanks
Stan

**Ron Coderre** · 08-31-2009, 04:11 PM

Can you post a sample workbook that exhibits your scenario?

**ChemistB** · 08-31-2009, 04:27 PM

It should read
...Type: =
...Hold down the CTRL and Press the UP ARROW one time
...Hold down CTRL and Press ENTER

Does that work for you?

**sj2487** · 08-31-2009, 05:02 PM

Thank you. I discovered that the workbook I am using has something that is preventing it because when used in another workbook it works.
Thank you.

**Ron Coderre** · 08-31-2009, 06:06 PM

Originally Posted by ChemistB

It should read
...Type: =
...Hold down the CTRL and Press the UP ARROW one time
...Hold down CTRL and Press ENTER

Actually, no....
Typing: = begins a formula
Pressing the UP arrow references the cell directly above the current cell.
CTRL-ENTER puts that formula into every selected blank cell.

Holding down CTRL when pressing the UP arrow would reference the first non-blank cell above the current cell. If that cell was 10 cells up from the current cell...every selected blank cell would reference a cell 10 cells up.