Sort and insert blank row after groups (Excel97)

[Shadowmis]

Hey, Here is a (pared-down) report I am trying to fix up. I want to sort decending by ColE and sort accending by ColA. (Such That All the 2's (in ColE) go first but within the 2's 1000's (in ColA) go first, then 2000's) After that I want to insert blank lines between each group in ColA, e.g. 1000's blank line 2000's blank line. I only need the blank lines for groups with a '2' in ColE, but blank lines between every group will not ruin anything.
A macro that would do that in one step would be really helpful.

[mdmackillop]

The following is 2003 code. I believe there are parts of the Sort routine incompatible with earlier versions, try deleting the DataOption bits at the end, failing which, if you can record your own sort and substitute it in the following code. The last bit should handle your blank row requirement.


Sub SplitData()
Range("A4:O4").Select
Range(Selection, Selection.End(xlDown)).Select
'Replace this section
Selection.Sort Key1:=Range("E4"), Order1:=xlDescending, Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveCell.Select
For i = 5 To ActiveSheet.UsedRange.Rows.Count
tmp1 = Int(Cells(i, 1) / 1000)
tmp2 = Int(Cells(i - 1, 1) / 1000)
If tmp1 - tmp2 >= 1 And tmp2 <> 0 Then
Cells(i, 1).EntireRow.Insert
End If
If Cells(i + 1, "E") <> 2 Then Exit Sub
Next
End Sub

[Shadowmis]

I got the sort working:) Can I insert a blank line after the last row with the number 2?

[mdmackillop]

Delete the line


If Cells(i + 1, "E") <> 2 Then Exit Sub

and substitute


If Cells(i + 1, "E") <> 2 Then
Cells(i + 1, 1).EntireRow.Insert
Exit Sub
End If

[Shadowmis]

Cool thanks!
I went to use this code in another file but got three errors about i, tmp1, and tmp2 not being defined.. I defined them and everything worked out well, but I am wondering why they did not need to be defined in the original?

[mdmackillop]

Hi,
Glad it's working.
I was a bit lazy and did not declare the variables. If you copied the code to another module headed Option Explicit, you would need to declare them. This is better coding as it uses memory more efficiently and helps avoid typos in the code variables.
The three variables should be declared as "Long"

[Shadowmis]

Thanks Again