Set variable sort range based on found text

[jeffbert]

I need to sort a worksheet from columns A10:AE, with the last row of the
sort procedure being two rows above where "XYZ" is found in column A. There
are blank cells in the range also.

For example:

XYZ is in cell A110. I need to sort the using the range A10:AE108

Any and all help is greatly appreciated.

[Jim Thomlinson]

This should be close... You did not specify how youwanted the data sorted so
I sorted by column A with no header row. You also did not specify a sheet so
you will have to change the code to suit...

Sub SortStuff()
Dim rngToSort As Range
Dim rngFound As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1")
Set rngFound = wks.Columns(1).Find(What:="xyz", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)
If rngFound Is Nothing Then
MsgBox "Sorry, couldn't find xyz"
Else
Set rngToSort = Range("AE10", rngFound.Offset(-2, 0))
rngToSort.Sort Key1:=Range("A10"), _
order1:=xlAscending, _
Header:=xlNo
End If

End Sub
--
HTH...

Jim Thomlinson


"jeffbert" wrote:

>
> I need to sort a worksheet from columns A10:AE, with the last row of the
> sort procedure being two rows above where "XYZ" is found in column A. There
> are blank cells in the range also.
>
> For example:
>
> XYZ is in cell A110. I need to sort the using the range A10:AE108
>
> Any and all help is greatly appreciated.