blank row after control break

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
how does that work, select the rows/cols and apply the macro against it or
run it on the entire sheet

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