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

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

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

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

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
>

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
>