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

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

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

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

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

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