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

On Wed, 5 Sep 2007 17:20:00 -0700, GregNga <(E-Mail Removed)>
wrote:

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

Thanks

"Gord Dibben" wrote:

> 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
>
> On Wed, 5 Sep 2007 17:20:00 -0700, GregNga <(E-Mail Removed)>
> wrote:
>
> >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)
>
>

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


On Thu, 6 Sep 2007 05:12:04 -0700, GregNga <(E-Mail Removed)>
wrote:

>how does that work, select the rows/cols and apply the macro against it or
>run it on the entire sheet
>
>Thanks
>
>"Gord Dibben" wrote:
>
>> 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
>>
>> On Wed, 5 Sep 2007 17:20:00 -0700, GregNga <(E-Mail Removed)>
>> wrote:
>>
>> >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)
>>
>>