Code to calculate the first row and last row of a range

[matpj]

Hi,

in my code I need to look at a worksheet's column A and find the occurance of a particular string.

Column A IS sorted so the string will be togther.
for example,

I'm looking for "hdw"
this occurs in rows 50-60
it is followed by a range of another string, so I need to find all rows that contain hdw.
I wish to set a two variables. 1 to the row number of the first instance of hdw (e.g. 50)
and the second for the last row (e.g. 60)

can anybody advise please?
thanks in advance,
Matt

[Bob Phillips]

Dim cell As Range
Dim iFirstRow As Long
Dim iLastRow As Long

On Error Resume Next
Set cell = ActiveSheet.Columns(1).Find("hdw", LookIn:=xlValues)
On Error GoTo 0
If Not cell Is Nothing Then
iFirstRow = cell.Row
iLastRow = iFirstRow
Do
iLastRow = iLastRow + 1
Loop Until Cells(iLastRow, "A").Value <> "hdw"
iLastRow = iLastRow - 1
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"matpj" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> in my code I need to look at a worksheet's column A and find the
> occurance of a particular string.
>
> Column A IS sorted so the string will be togther.
> for example,
>
> I'm looking for "hdw"
> this occurs in rows 50-60
> it is followed by a range of another string, so I need to find all rows
> that contain hdw.
> I wish to set a two variables. 1 to the row number of the first
> instance of hdw (e.g. 50)
> and the second for the last row (e.g. 60)
>
> can anybody advise please?
> thanks in advance,
> Matt
>
>
> --
> matpj
> ------------------------------------------------------------------------
> matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
> View this thread: http://www.excelforum.com/showthread...hreadid=514818
>