I have an inventory spreadsheet that uses column A as a site location and
column B as a part number. Is there any way to insert a blank row if A2 does
not equal A1 and A1 is not blank? The locations typically contain anywhere
from 1 to 20 items and the sheet is a little easier to read if I can seperate
each location with a blank row.

Also, is there any way to do a conditional format to fill cells of blank
rows with a specific color? I've tried using a formula for the CF but I
can't figure out how to get the formula to use a variable. For example, if
A2 and A10 are empty I would like only row 2 and row 10 filled with grey. So
far the only CF I've been been able to do with a formula is get all rows to
fill with grey if A2 is empty.

Hi,

There is nothing in Excel that will automaticlally insert a blank row,
however, you could program it to do so. But before I suggest doing this,
placing blank rows in your data just of looks is generally a bad idea. This
destroys the structured nature of Excel, it sort of violates the spreadsheet
paradym. It means that you can't sort your data, that all commands that work
well with blocks of data, such as pivot table, data validations, subtotal,
auto filter, advanced filter, will require manually selecting the range
anytime you want to do anything. Likewise for AutoSum. In addition
selection techniques such as Ctrl Down Arrow, Ctrl Up Arrow will be useless.

i would recommend either using an altenating color scheme, such as that
using in 2007 for tables and pivot tables or increasing the row height where
you would normally have a separator row.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Shawn" wrote:

I have an inventory spreadsheet that uses column A as a site location and
column B as a part number. Is there any way to insert a blank row if A2 does
not equal A1 and A1 is not blank? The locations typically contain anywhere
from 1 to 20 items and the sheet is a little easier to read if I can seperate
each location with a blank row.

Also, is there any way to do a conditional format to fill cells of blank
rows with a specific color? I've tried using a formula for the CF but I
can't figure out how to get the formula to use a variable. For example, if
A2 and A10 are empty I would like only row 2 and row 10 filled with grey. So
far the only CF I've been been able to do with a formula is get all rows to
fill with grey if A2 is empty.

Actually, you don't need a macro:
In Cell C2 enter
=IF(A2<A3,1,"X") and fill down as far as your data run
While col C is selected, use edit/goto special, click Formulas, and Deselect
"Text", "Logicals" and "Errors" leaving only " Numbers".
Now only the X's are selected. Do Insert/Rows and you're done!
Clear col C.

Hope this is of help. Pls click Yes if this help
Thanks

cheers,


"Shawn" wrote:

I have an inventory spreadsheet that uses column A as a site location and
column B as a part number. Is there any way to insert a blank row if A2 does
not equal A1 and A1 is not blank? The locations typically contain anywhere
from 1 to 20 items and the sheet is a little easier to read if I can seperate
each location with a blank row.

Also, is there any way to do a conditional format to fill cells of blank
rows with a specific color? I've tried using a formula for the CF but I
can't figure out how to get the formula to use a variable. For example, if
A2 and A10 are empty I would like only row 2 and row 10 filled with grey. So
far the only CF I've been been able to do with a formula is get all rows to
fill with grey if A2 is empty.

xlmate,

Nice. Minor bug - if more than two consecutive rows are different (eg.
A2<A3<A4)the 'insert' inserts two empty rows between 2 & 3 and no empty row
between 3 & 4. I'm still working on that one but you've got me going in the
right direction.

"xlmate" wrote:

Actually, you don't need a macro:
In Cell C2 enter
=IF(A2<A3,1,"X") and fill down as far as your data run
While col C is selected, use edit/goto special, click Formulas, and Deselect
"Text", "Logicals" and "Errors" leaving only " Numbers".
Now only the X's are selected. Do Insert/Rows and you're done!
Clear col C.

Hope this is of help. Pls click Yes if this help
Thanks

cheers,


"Shawn" wrote:

I have an inventory spreadsheet that uses column A as a site location and
column B as a part number. Is there any way to insert a blank row if A2 does
not equal A1 and A1 is not blank? The locations typically contain anywhere
from 1 to 20 items and the sheet is a little easier to read if I can seperate
each location with a blank row.

Also, is there any way to do a conditional format to fill cells of blank
rows with a specific color? I've tried using a formula for the CF but I
can't figure out how to get the formula to use a variable. For example, if
A2 and A10 are empty I would like only row 2 and row 10 filled with grey. So
far the only CF I've been been able to do with a formula is get all rows to
fill with grey if A2 is empty.

Shawn

glad to be of help. Let mw know if you need assistance
Pls click Yes if this have help

cheers

"Shawn" wrote:

xlmate,

Nice. Minor bug - if more than two consecutive rows are different (eg.
A2<A3<A4)the 'insert' inserts two empty rows between 2 & 3 and no empty row
between 3 & 4. I'm still working on that one but you've got me going in the
right direction.

"xlmate" wrote:

Actually, you don't need a macro:
In Cell C2 enter
=IF(A2<A3,1,"X") and fill down as far as your data run
While col C is selected, use edit/goto special, click Formulas, and Deselect
"Text", "Logicals" and "Errors" leaving only " Numbers".
Now only the X's are selected. Do Insert/Rows and you're done!
Clear col C.

Hope this is of help. Pls click Yes if this help
Thanks

cheers,


"Shawn" wrote:

I have an inventory spreadsheet that uses column A as a site location and
column B as a part number. Is there any way to insert a blank row if A2 does
not equal A1 and A1 is not blank? The locations typically contain anywhere
from 1 to 20 items and the sheet is a little easier to read if I can seperate
each location with a blank row.

Also, is there any way to do a conditional format to fill cells of blank
rows with a specific color? I've tried using a formula for the CF but I
can't figure out how to get the formula to use a variable. For example, if
A2 and A10 are empty I would like only row 2 and row 10 filled with grey. So
far the only CF I've been been able to do with a formula is get all rows to
fill with grey if A2 is empty.