Results 1 to 4 of 4
Like Tree1Likes
  • 1 Post By Firefly2012

Find first and last row that meet a certain value

This is a discussion on Find first and last row that meet a certain value within the Excel Questions forums, part of the Question Forums category; Hi guys, I'm hoping someone can help me here, what I'm trying to do is first the first and last ...

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    98

    Default Find first and last row that meet a certain value

    Hi guys,

    I'm hoping someone can help me here,

    what I'm trying to do is first the first and last rows that contain a certain value and then select everything in between as a range.
    so if for example cell B1 has the value "bbb" it will select A3:A5 as a range.

    Value
    aaa
    aaa
    bbb
    bbb
    bbb
    ccc

    Hope I'm making sense guys, appreicate your help.

    I'm using Excel 2010

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Find first and last row that meet a certain value

    This is one possibility:
    Code:
    Sub selectRange()
    Dim rngFirst As Range
    Dim rngLast As Range
    
    Set rngLast = Range("A:A").Find(What:=Range("B1").Value, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlPrevious)
    
    If Not rngLast Is Nothing Then
        Set rngFirst = Range("A:A").Find(What:=Range("B1").Value, after:=rngLast, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext) 'note change in searchdirection!
        Range(rngFirst, rngLast).Select
    Else
        MsgBox Range("B1").Value & " not found in column A!"
    End If
    End Sub
    Pretty1996 likes this.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    98

    Default Re: Find first and last row that meet a certain value

    firefly,

    That's perfect.

    Thanks for the quick reply, I will come back if I have any issues

  4. #4
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Find first and last row that meet a certain value

    You're welcome!
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com