Insert rows into a sorted range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FIRSTROUNDKO - 20 Mar 2006 14:24 GMT

Hi,

how do I insert rows into a sorted range, when the sort changes from one row
to the next

Thanks

Darren

Reply to this Message

Dave Peterson - 20 Mar 2006 15:45 GMT

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 - 8 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200603/1

Signature

Dave Peterson

Reply to this Message

FIRSTROUNDKO - 20 Mar 2006 16:34 GMT

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

Reply to this Message

FIRSTROUNDKO - 20 Mar 2006 17:24 GMT

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

>I have'nt since I need to do this a few hundred times when I produce
>remittances
[quoted text clipped - 4 lines]
>>>
>>> Darren

Reply to this Message

Tom Ogilvy - 20 Mar 2006 18:06 GMT

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.

Signature

Regards,
Tom Ogilvy

> I can adapt this from another post but i need the next line after the to stop
> the loop after a empty row
[quoted text clipped - 23 lines]
> >>>
> >>> Darren

Reply to this Message

Tom Ogilvy - 20 Mar 2006 17:27 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 4 lines]
>
> Darren

Reply to this Message