Find The Row Values of the First and Last Occurences of a Value In A Column

[Ark68]

I am looking for some assistance on creating a vba solution to find the row values of first and last instance of a particular value in a dynamic range, assigning them to dstart and dend respectively.

The range is dynamic defined by "C" & pdaStart & "C" & pdaEnd. In our example, the range would be C13:C58.
The value we're looking for is "D"

WS 24-May-22.xlsx
	R
12
13	D
14	D
15	D
16	D
17	D
18	D
19	D
20	D
21	D
22	D
23	D
24	D
25	D
26	D
27	D
28	D
29	D
30	D
31	D
32	D
33
34	F
35	F
36	F
37	F
38	F
39	F
40	F
41	F
42	F
43	F
44	F
45	F
46	F
47	F
48	F
49	F
50	F
51
52	C
53	C
54	C
55	C
56
57	P
58	P
59
Master

With the above data capture, I am looking for the value of dstart = 13, and the value for dend = 32.

 

[JoeMo]

Assuming you are looking for "D" in column R per your posted data:

Sub FirstAndLastRows()
Const LkFor As Variant = "D" 'Change to suit
Dim R As Range, Fnd As Range, dstart As Long, dend As Long, firstAdr As String, msg As String
Set R = Range("R13:R58") ' change range to suit
Set Fnd = R.Find(what:=LkFor, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
If Fnd Is Nothing Then
    MsgBox LkFor & " not found in column R"
    Exit Sub
End If
firstAdr = Fnd.Address
dstart = Fnd.Row
Do
    Set Fnd = R.FindNext(Fnd)
    If Fnd Is Nothing Or Fnd.Address = firstAdr Then
        If dend = 0 Then
            MsgBox "Only one cell in column R contains the value " & LkFor & " in row " & dstart
            Exit Sub
        End If
    ElseIf Fnd.Row > dend Then
        dend = Fnd.Row
    End If
Loop While Not Fnd Is Nothing And Fnd.Address <> firstAdr
msg = "The first row in range " & R.Address(0, 0) & " containing " & LkFor & " is row " & dstart & " and "
msg = msg & "the last row is " & dend
MsgBox msg
End Sub

 

[johnnyL]

Wow. I see I was late to the party, didn't realize a response was received. Since I am here, I figured I would post what I came up with. Serves me right for getting caught up watching tv while working on a solution...

Sub FindFirstAndLastMatch()
'
    Dim dend                As Long, dstart As Long
    Dim SearchColumnNumber  As Long
    Dim ValueToFind         As String
'
    SearchColumnNumber = 18                                                         ' <--- Set this to the column # to look through ... 18 = Column R
    ValueToFind = "D"                                                               ' <--- Set this to the value you are looking for
'
    On Error GoTo ErrorHandler                                                      ' If error occurs, Let user know, and then exit
'
    dstart = Columns(SearchColumnNumber).Find(What:=ValueToFind, LookAt:=xlWhole, _
            SearchDirection:=xlNext, MatchCase:=False).Row                          ' Find row number of the 1st occurrence of ValueToFind
    dend = Columns(SearchColumnNumber).Find(What:=ValueToFind, LookAt:=xlWhole, _
            SearchDirection:=xlPrevious, MatchCase:=False).Row                      ' Find row number of the last occurrence of ValueToFind
'
    MsgBox "First occurrence of '" & ValueToFind & " occurred at " & _
            Split(Cells(1, SearchColumnNumber).Address, "$")(1) & dstart & vbCrLf _
            & vbCrLf & "Last occurrence occurred at " & _
            Split(Cells(1, SearchColumnNumber).Address, "$")(1) & dend              ' Display to user the locations of 1st & last occurrence
    Exit Sub                                                                        ' Exit
ErrorHandler:
    MsgBox "Error encountered! At least one of the values was not found in the searched column."
End Sub

 

[Ark68]

Thank you both for your help. There are concepts in those solutions that I know I would not have been able to come up with my limited (but improving) skillset.

Joe, your solution works wonderfully. One thing I had to do is change

Set R = Range("R13:R58")

to

Set R = Range("R12:R58")

Although the first instance of 'D' is actually on row 13, the value of dstart always returned row #14.

And, before I go and mess something up, you had in your solution an error checking routine should only one instance of 'D' exists. This is possible in my application. Is it appropriate in the code you provided to change:

If Fnd Is Nothing Or Fnd.Address = firstAdr Then
        If dend = 0 Then
            MsgBox "Only one cell in column R contains the value " & LkFor & " in row " & dstart
            Exit Sub
        End If
ElseIf Fnd.Row > dend Then

to

If Fnd Is Nothing Or Fnd.Address = firstAdr Then
        If dend = 0 Then
            dend = dstart
            Exit Sub
        End If
ElseIf Fnd.Row > dend Then

JohnnyL, although I hadn't tested your code, I am confident it will work. I see the approach you took. You used the Find function to search in two directions, down (xlNext) to find the first value, and up (xlPrevious) to find the last value.

 

[JoeMo]

Thank you both for your help. There are concepts in those solutions that I know I would not have been able to come up with my limited (but improving) skillset.

Joe, your solution works wonderfully. One thing I had to do is change

Set R = Range("R13:R58")

to

Set R = Range("R12:R58")

Although the first instance of 'D' is actually on row 13, the value of dstart always returned row #14.

And, before I go and mess something up, you had in your solution an error checking routine should only one instance of 'D' exists. This is possible in my application. Is it appropriate in the code you provided to change:

If Fnd Is Nothing Or Fnd.Address = firstAdr Then
        If dend = 0 Then
            MsgBox "Only one cell in column R contains the value " & LkFor & " in row " & dstart
            Exit Sub
        End If
ElseIf Fnd.Row > dend Then

to

If Fnd Is Nothing Or Fnd.Address = firstAdr Then
        If dend = 0 Then
            dend = dstart
            Exit Sub
        End If
ElseIf Fnd.Row > dend Then

Your suggested change looks OK except if there is only one value in the range it makes dend = dstart and then promptly exits the sub w/o letting the user know via the msgbox I had placed just prior to Exit Sub.

 

[johnnyL]

Joe, your solution works wonderfully. One thing I had to do is change

Set R = Range("R13:R58")

to

Set R = Range("R12:R58")

Although the first instance of 'D' is actually on row 13, the value of dstart always returned row #14.

The proper way to fix that issue lies within the Find line of code. I say 'proper' way because if, for example, your data started in Row 1, you wouldn't be able to subtract one from that to have it find the result from row 1. The way Find works is it starts looking at the next address in the specified range. To have it work as you would expect it to work you tell Find to 'start' with the last address in the range. That way it will actually 'loop' back to the beginning of the range as the first address it checks when it starts.

So replace:

Set Fnd = R.Find(what:=LkFor, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)

With:

Set Fnd = R.Find(what:=LkFor, After:=Range("" & R.Cells(R.Rows.Count, R.Columns.Count).Address(0, 0) & ""), _
        LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)

That After:=Range("" & R.Cells(R.Rows.Count, R.Columns.Count).Address(0, 0) & "") part is the correction I mentioned to tell Find to start looking AFTER the last address in the range ... AKA the first address in the range because it 'loops' back around to the beginning.

 

[Ark68]

Joe, thank you. I've kept the message ion for now while I test just to make sure everything is going as planned.
johnnyL ... I have replaced my code with your suggestion. So far so good!

Thank you all!