How do I use a macro to select a range?
 

Dim sStr as String
Dim rng as Range, rng1 as Range
Dim rng2 as Range

sStr ="GJ"
Set rng = Columns(2).Find(What:=sStr, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set rng1 = Columns(2).Find(What:=sStr, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If not rng is nothing then
set rng2 = Range(rng,rng1)

if rng2.count < 1 then
rng2.Resize(,5).Sort Key1:=rng1.offset(0,1), Header:=xlNo
end if
End if


--
Regards,
Tom Ogilvy




"CBS" wrote in message
...
Excel 2000

I have a sorted list 5 columns wide. Column B has the value GJ in the
middle group of rows. The number of rows changes each month; the starting
row number with GJ changes each month as does the number of rows that
contain GJ. The middle group always has GJ in column B.

How can I write a macro that finds all the "GJ" rows and then sorts them
by
column C?

When I manually do this, I find the first GJ row, hold down the shift key
to
anchor the curser, then scroll down to the last row that has GJ, then
arrow
right to the 5th column. I haven't been able to duplicate this action
using
a macro.