Subtotal Function - add formula to all blank rows
 

Hi:
You all probably know that when you use the subtotal function it adds a
blank line with totals below the columns that you specify. Is there a way to
get the blank lines to fill using the row above the blank cell? I can add a
formula manually but I have over 50 spreadsheets so I hoped that there was a
code that would add the contents of the row above if the cell is blank.

Example:
Columns -
Customer Name SalesRep Total Sales$

no data appears in the Customer Name and Sales Rep - only Total Sales$ since
that column holds the subtotals. I need a code that will say if the cell is
blank after subtotal then add the contents of the row (cell) above. This will
show me the data in each row once I expand the (+) to the left. Please help...

Subtotal Function - add formula to all blank rows
 

Nikki wrote:
Hi:
You all probably know that when you use the subtotal function it adds a
blank line with totals below the columns that you specify. Is there a way to
get the blank lines to fill using the row above the blank cell? I can add a
formula manually but I have over 50 spreadsheets so I hoped that there was a
code that would add the contents of the row above if the cell is blank.

Example:
Columns -
Customer Name SalesRep Total Sales$

no data appears in the Customer Name and Sales Rep - only Total Sales$ since
that column holds the subtotals. I need a code that will say if the cell is
blank after subtotal then add the contents of the row (cell) above. This will
show me the data in each row once I expand the (+) to the left. Please help...






First, consider a PivotTable instead of SubTotals.

Second, if you insist on SubTotals, after adding them, collapse the detail so
only the SubTotal lines are shown. Select the first visible cell in one of the
blank columns. If it is cell B23, add the formula "=B22" (without the quotes).
This should give you the result you want. Copy that cell, then select all of
the cells you want filled. Select Edit / Go To / Special / Visible Cells Only /
OK / Edit / Paste and you are done.