Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows into a sorted range
Hi,
how do I insert rows into a sorted range, when the sort changes from one row to the next Thanks Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows into a sorted range
You could use a macro, but have you considered using Data|subtotals?
It's built into excel and it even provides subtotals per group. "FIRSTROUNDKO via OfficeKB.com" wrote: Hi, how do I insert rows into a sorted range, when the sort changes from one row to the next Thanks Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows into a sorted range
I have'nt since I need to do this a few hundred times when I produce
remittances Dave Peterson wrote: You could use a macro, but have you considered using Data|subtotals? It's built into excel and it even provides subtotals per group. Hi, [quoted text clipped - 4 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows into a sorted range
I can adapt this from another post but i need the next line after the to stop
the loop after a empty row Sub Deilv() Dim LastRow As Long Dim row_index As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 Step -1 If Cells(row_index, "B").Value < _ Cells(row_index + 1, "B").Value Then Cells(row_index + 1, "B").Resize(26).EntireRow. _ Insert Shift:=xlDown End If Next Application.ScreenUpdating = True End Sub FIRSTROUNDKO wrote: I have'nt since I need to do this a few hundred times when I produce remittances You could use a macro, but have you considered using Data|subtotals? [quoted text clipped - 5 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows into a sorted range
assume determination is made on column A
Sub addrows() dim lastrow as Long Dim i as Long lastrow = cells(rows.count,"A").End(xlup).row for i = lastrow-1 to 1 step -1 if cells(i,"A").Value < cells(i+1,"A").Value then rows(i+1).Insert end if Next End Sub -- Regards, Tom Ogilvy "FIRSTROUNDKO via OfficeKB.com" wrote: Hi, how do I insert rows into a sorted range, when the sort changes from one row to the next Thanks Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows into a sorted range
Sub Deilv()
Dim LastRow As Long Dim row_index As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 Step -1 if Cells(row_Index,"B").Value = "" then goto GetOut If Cells(row_index, "B").Value < _ Cells(row_index + 1, "B").Value Then Cells(row_index + 1, "B").Resize(26).EntireRow. _ Insert Shift:=xlDown End If Next GetOut: Application.ScreenUpdating = True End Sub You could just do if Cells(row_Index,"B").Value = "" then exit sub and application.screenUpdating is turned on by default. -- Regards, Tom Ogilvy "FIRSTROUNDKO via OfficeKB.com" wrote: I can adapt this from another post but i need the next line after the to stop the loop after a empty row Sub Deilv() Dim LastRow As Long Dim row_index As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 Step -1 If Cells(row_index, "B").Value < _ Cells(row_index + 1, "B").Value Then Cells(row_index + 1, "B").Resize(26).EntireRow. _ Insert Shift:=xlDown End If Next Application.ScreenUpdating = True End Sub FIRSTROUNDKO wrote: I have'nt since I need to do this a few hundred times when I produce remittances You could use a macro, but have you considered using Data|subtotals? [quoted text clipped - 5 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can three separate sections of rows be sorted in the same order? | Excel Worksheet Functions | |||
How can I insert and delete rows within a locked cell range column | Excel Worksheet Functions | |||
How do I insert rows between sorted data | Excel Discussion (Misc queries) | |||
prevent row insert in a range of rows | Excel Programming | |||
Sort one column and entire rows get sorted | Excel Programming |