Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank row after control break
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank row after control break
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 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank row after control break
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 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank row after control break
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 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 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
spinner format control has no control tab | Excel Worksheet Functions | |||
Skipping line on control break | Excel Discussion (Misc queries) | |||
Cannot Control - Break out of VBA | Excel Discussion (Misc queries) | |||
Control Tab from Combo box- format control missing!! | Excel Discussion (Misc queries) |