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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

matpj - 21 Feb 2006 10:13 GMT

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

Signature

matpj

Reply to this Message

Bob Phillips - 21 Feb 2006 10:32 GMT

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

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hi,
>
[quoted text clipped - 15 lines]
> thanks in advance,
> Matt

Reply to this Message