Inserting rows macro help
Posted by Tom Urtis on January 31, 2001 6:02 PM
I need to enhance a sort macro by adding VBA code that inserts a blank row in between geographic regions. The VBA code must accommodate new data (records) being added to this list every day, so that each region always sorts and is then separated by a blank row. Example:
Before sorting:
A B
1 North 725
2 West 825
3 East 740
4 South 390
5 East 155
6 North 650
After sorting:
A B
1 East 155
2 East 740
3
4 North 650
5 North 725
6
7 South 390
8
9 West 825
Does anyone know what code can be added to my existing sort macro to make this happen? Thank you in advance for any assistance!!
Tom Urtis
Posted by Barrie Davidson on February 01, 2001 2:13 PM
Try inserting this after your sort macro (you'll need to declare "Area" and "Area2" as variables and you need to select the first cell in column B before this code).
Area = InStr(Selection.Value, " ")
Area = Mid(Selection.Value, 1, Area - 1)
Selection.Offset(1, 0).Select
Do Until Selection.Value = ""
Area2 = InStr(Selection.Value, " ")
Area2 = Mid(Selection.Value, 1, Area2 - 1)
If Area <> Area2 Then
Selection.EntireRow.Insert
Area = Area2
Else
End If
Selection.Offset(1, 0).Select
Loop
