+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    164

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

    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

  2. #2
    Bob Phillips
    Guest

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

    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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1