blank row after control break

G

GregNga

I am listing customer accounts, sorted by by company#, account. How can I
leave a blank row when there is a break on company# (without creating a
pivot table)
 
G

Gord Dibben

How about a macro? Assuming Company # is 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
 
G

GregNga

how does that work, select the rows/cols and apply the macro against it or
run it on the entire sheet

Thanks
 
G

Gord Dibben

You can run it on the entire sheet without any selection.

The code runs through the used range on Column A(1) from the bottom up and
inserts a blank row at any change in Company #


Gord
 
Top