First, I wouldn't put that extra row in my data. It makes lots of things just a
little more difficult--including sorts, pivottables, charts, subtotals....

What I would do is either put a border above the first row of each group (or
even just double the rowheight of the the first row of each group. It'll look
double spaced, but not screw up my data.

But if your data in column A is text (not formulas), try this:

Option Explicit
Sub testme()

Dim myArea As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea.CurrentRegion
.Sort key1:=.Range("b1"), order1:=xlAscending, _
key2:=.Range("d1"), order1:=xlAscending, _
header:=xlNo, MatchCase:=False
End With
Next myArea
End With

End Sub

If you click on the first cell in a group and hit Edit|goto special|Current
Region, you'll see how it's selecting the range to sort. (Ctrl-* is also a
shortcut for this.)


"Scottmk <" wrote:

Sorry for the confusion: By row break, all I meant is that I skipped a
row inbetween classes. I tried out what I had mentioned and sure
enough, it got screwed up on the reference issue. Basically, I need
code that will be able to start at the top of a range, select all
underneath until there is a blank rowsort itthen go down to the next
group etc etc.
This is what I have so far (which works fine until I insert or delete
rows in the sheet):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/9/2004 by Scott
'

'
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Key2:=Range("D3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A64").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B64"), Order1:=xlAscending,
Key2:=Range("D64" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A88").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B88"), Order1:=xlAscending,
Key2:=Range("D88" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.End(xlDown).Select
Range("A138").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B138"), Order1:=xlAscending,
Key2:=Range( _
"D138"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
_
DataOption2:=xlSortNormal
End Sub

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson