insert row using conditional formatting

  • Thread starter Thread starter peabody
  • Start date Start date
P

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.
 
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
 
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
 
Back
Top