insert row using conditional formatting

[peabody]

I have a fairly large worksheet with a number of stores listed and their
sales for the year. Some stores have entries for 10 years, some have only
one. I would like to insert a blank row between the different stores,
keeping the same store info together. Is there any way to do this with
conditional formatting or a macro? Doing it manually would take way too much
time.

 

[FSt1]

hi
not with conditional formating but you can do it with a macro.
how is your data layed out? since you are wanting to add a row between
stores, i assume that is a solid block of data.
what column has the key that distinguishes one store form another?
post back and i'll work on it for you.

regards
FSt1

 

[Gord Dibben]

Not with CF but here is a macro from Sandy Mann

Assumes store names are in column A

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP