I have a worksheet with 300 rows and 5 columns and I need an ability to run a
macro to sort the worksheet. I know that is simple enouh to record but my
problem is I need to (through code) determine the end range for the sort.

For example if cell A236 equals "ZZZ" then I want the end range of the sort
to be row 235.

Any thought how I can pull this off

Sub find_a_string()
Set b = Range("A:A").Find("ZZZ", lookat:=xlWhole)
MsgBox b.Row
End Sub

Range("A1:E" & b.Row - 1) would be the sort range.


Gord Dibben MS Excel MVP


On Thu, 18 Jun 2009 14:06:01 -0700, Pats
wrote:

I have a worksheet with 300 rows and 5 columns and I need an ability to run a
macro to sort the worksheet. I know that is simple enouh to record but my
problem is I need to (through code) determine the end range for the sort.

For example if cell A236 equals "ZZZ" then I want the end range of the sort
to be row 235.

Any thought how I can pull this off

Thank you sir,

I found the error of my way and all is working great.

"Gord Dibben" wrote:

Sub find_a_string()
Set b = Range("A:A").Find("ZZZ", lookat:=xlWhole)
MsgBox b.Row
End Sub

Range("A1:E" & b.Row - 1) would be the sort range.


Gord Dibben MS Excel MVP


On Thu, 18 Jun 2009 14:06:01 -0700, Pats
wrote:

I have a worksheet with 300 rows and 5 columns and I need an ability to run a
macro to sort the worksheet. I know that is simple enouh to record but my
problem is I need to (through code) determine the end range for the sort.

For example if cell A236 equals "ZZZ" then I want the end range of the sort
to be row 235.

Any thought how I can pull this off

Here is the code modified to use your sugestion. Below that is the origanal
sort code.

Sub AlphaSort()
'
' AlphaSort Macro
'

'

Range("A1:E" & b.Row - 1).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("A4:E" & b.Row - 1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:E" & b.Row - 1)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B4").Select
End Sub

------------------------------------------------------------------------------------------------

Sub AlphaSort()
'
' AlphaSort Macro
'

'
Range("A4:E105").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("A4:A106") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A4:E105")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("B4").Select
End Sub