Help sorting and inserting rows

[Parrot_Head]

Hello,

I need assistance please with coding the attached sheet. I'm currently running MS Excel 2000.

What I need the code for is:
1. Sort by Column C (ascending)
2. Sort by Column Q (ascending)
3. Sort by Column S (ascending)
4. Then insert a row when the value changes in Column C

The rest of the macro is finished, I just can?t get the inserting of the rows to work.

Note: This spreadsheet can be a little as 20 rows or as much as 2500 rows if that makes any difference.

Thank you so much for any advice or help with this problem,

[acw]

Hi

Try the following

Sub aaa()
Range("a1").CurrentRegion.Select
Selection.Sort key1:=Range("c1"), order1:=xlAscending, key2:=Range("q1"), _
order1:=xlAscending, key3:=Range("s1"), order3:=xlAscending, _
header:=xlYes
For i = Cells(Rows.Count, "C").End(xlUp).Row To 3 Step -1
If Cells(i - 1, "C") <> Cells(i, "C") Then
Cells(i, "C").EntireRow.Insert
i = i - 1
End If
Next i
End Sub



HTH

Tony

[Parrot_Head]

Thanks - I'll let you know

[ALe]

If you need to do it without VBA, a rough way is to order your data and then insert Subtotals.

[lucas]

I added a line break to your code acw so it doesn't go off the page...

[Norie]

Here's an alternative.

Sub SortAndInsert()
Dim rng As Range
Set rng = Range("A1").CurrentRegion

rng.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("Q1"), _
Order1:=xlAscending, Key3:=Range("S1"), Order3:=xlAscending, _
Header:=xlYes

Set rng = Range("C2")
While rng.Value <> ""
If rng.Value <> rng.Offset(1) Then
rng.Offset(1).EntireRow.Insert
Set rng = rng.Offset(1)
End If
Set rng = rng.Offset(1)
Wend
End Sub

[zoom38]

I have nothing to add, just wanted to subscribe to this thread for future reference.

[mdmackillop]

Hi Zoom,
There is a "Subscribe Thread" option in the Thread Tools dropdown.
Regards
MD

[Parrot_Head]

Works great - thanks for the help!!!

[Zack Barresse]

Tony, as a side note ..

Cells(Rows.Count, "C").End(xlUp).Row

.. will fail in XL 2007. Instead, use the column number instead of a string ..

Cells(Rows.Count, 3).End(xlUp).Row

This should be XL 97-2007 compliant. (Heck, they might fix that before the final release, don't know, certainly screwed me up though!)

[Norie]

Zack

So they've removed the ability to use the column letter with Cells.:eek:

That was a useful feature.

[Zack Barresse]

I don't know. It bombed on the beta though.