Sorting blocks of data

[Bob Wall]

Xl 97 question. I have a worksheet with several blocks of data, all
beginning in Column A.
I need to move from block to block, sorting each one independently. I
have no problem setting the range to be sorted, but I'm having a brain
cramp about how to move from one block to the next down through column
A.

Each block is different number of rows, but always the same number of
columns wide.

Thanks in advance!

[Wilson]

How are the blocks differentiated??
"Bob Wall" <rbw...@fedex.com> wrote in message
news:3CA8C693...@fedex.com...

[Bob Wall]

Sorry, should have stated that - there are a few blanks rows between the end
of one and the beginning of the next. I'd like to start at Range A1 and
progress down through each block, I just need help with the DIM and For/Next
statements...

TIA

[Dave Peterson]

If your data in column A is values (not formulas), then maybe this would work.

Option Explicit

Sub testme333()

Dim const_rng As Range
Dim form_rng As Range
Dim const_form_rng As Range

Dim myRange As Range
Dim myArea As Range

Set myRange = Range("a1", Cells(Rows.Count, "a").End(xlUp))

If Application.CountA(myRange) < 2 Then
MsgBox "Put some data in column A!"
Exit Sub
End If

On Error Resume Next
Set const_rng = myRange.SpecialCells(xlCellTypeConstants)
Set form_rng = myRange.SpecialCells(xlCellTypeFormulas)
Set const_form_rng = Union(const_rng, form_rng)
On Error GoTo 0

If const_form_rng Is Nothing Then
If Not const_rng Is Nothing Then
Set const_form_rng = const_rng
End If
If Not form_rng Is Nothing Then
Set const_form_rng = form_rng
End If
End If

If const_form_rng Is Nothing Then
MsgBox "nothing in column A"
Else
For Each myArea In const_form_rng.Areas
myArea.Resize(, 8).Sort Key1:=myArea(1).Offset(0, 4), _
order1:=xlAscending, _
Header:=xlNo
Next myArea
End If

End Sub

(this looks for any formulas and any constants in column A. Then operates
against each separate piece.)

I resized the range to be 8 columns wide. And used .0ffset(0,3) as the sort
key. (.offset(0,3) = column D if the range is in A.)

--

Dave Peterson
ec3...@msn.com

[Tom Ogilvy]

Dim rng as Range
Set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
Set rng = rng.SpecialCells(xlConstants)

for each rngArea in rng.Areas
rngArea.Resize(,5).Sort . . .
Next

change 5 to the number of columns.

Regards,
Tom Ogilvy

Bob Wall <rbw...@fedex.com> wrote in message

news:3CA8C8E2...@fedex.com...

[Dave Peterson]

Oops. I changed my mind after I started the response. It'll work for both
constants and formulas!

--

Dave Peterson
ec3...@msn.com

[Bob Wall]

Thanks Tom and Dave - I used Tom's code, as it was shorter: it worked perfectly.

As always, thanks to both of you for your help!

BW

[Tom Ogilvy]

It is only shorter because I assumed you had nothing but hard coded values
in your cells. Dave accounted for the fact that there could be formulas as
well - which takes a bit more code. If there is a mixture of formulas and
hard coded values within the rows of a block, I think it would take even
more code - but it sounds like the original assumption of just values fits
your case.

Regards,
Tom Ogilvy

"Bob Wall" <rbw...@fedex.com> wrote in message

news:3CA92AC4...@fedex.com...