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