Find Row Of Last Instance of A Value (A Break In The Series) From The Top Of A Column

[Ark68]

I have Googled and I have searched this forum, but my results are only as good as the criteria I used to search.
I found a lot of references to similar concepts, but nothing exclusiver to the uniqueness of my quest.

Is anyone able to suggest a VBA solution to find the row number with the last instance of the number 1 from the top of column W of my worksheet?

Book1
	W
1	1
2	1
3	0
4	0
5	0
6	0
7	1
8	0
9	0
10	0
11	1
12	1
13	1
14	0
15	0
16	0
17	0
Sheet2

In this case, I'm looking to find a row number of 2.

 

[Trebor76]

Hi Ark68,

Try this:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim strMyCol As String
    Dim varMyValue As Variant
    
    Application.ScreenUpdating = False
    
    strMyCol = "W"
    varMyValue = Range(strMyCol & "1")
    
    For Each rngMyCell In Range(strMyCol & "1:" & strMyCol & Cells(Rows.Count, strMyCol).End(xlUp).Row)
        If CVar(rngMyCell.Offset(1, 0)) <> varMyValue Then
            Exit For
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox rngMyCell.Row

End Sub

Just change the strMyCol variable to suit if required.

Regards,

Robert

 

[Ark68]

Thank you Robert. I think I may have messed up...

        With ws_data
            strMyCol = "W"
            varMyValue = .Range(strMyCol & "1")
    
            For Each rngMyCell In .Range(strMyCol & "1:" & strMyCol & .Cells(Rows.count, strMyCol).End(xlUp).row)
                If CVar(rngMyCell.offset(1, 0)) <> varMyValue Then
                    Exit For
                End If
            Next rngMyCell
        End With
        
        Application.ScreenUpdating = True
        MsgBox rngMyCell.row

The result I am getting, is 1, which is the header row. W1 is actually empty,it doesn't have any value in it.

I changed this line to start at row 2 intead of 1.

For Each rngMyCell In .Range(strMyCol & "2:" & strMyCol & .Cells(Rows.count, strMyCol).End(xlUp).row)

This gives me a value of 2. Cell W2 does have a value of 1 in it, but it's not the last in the series. W3 is the last cell with 1 in it, with W4 = 0.

 

[Trebor76]

The cell reference the varMyValue variable was pointing to had to be changed as well i.e. varMyValue = .Range(strMyCol & "2")

Try this where both the source column and starting row are variables and so once set they are applicable for the remainder of the code:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim strMyCol As String
    Dim varMyValue As Variant
    Dim lngStartRow As Long
    
    Application.ScreenUpdating = False
    
    strMyCol = "W"
    lngStartRow = 2
    varMyValue = Range(strMyCol & lngStartRow)
    
    For Each rngMyCell In Range(strMyCol & lngStartRow & ":" & strMyCol & Cells(Rows.Count, strMyCol).End(xlUp).Row)
        If CVar(rngMyCell.Offset(1, 0)) <> varMyValue Then
            Exit For
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox rngMyCell.Row

End Sub

Regards,

Robert

 

[Ark68]

Thanks Robert for your continued support. This seems to be performing a bit better, but I can't seem to get the result.

        With ws_data
            strMyCol = "W"
            lngStartRow = 2
            varMyValue = .Range(strMyCol & lngStartRow)
    
            For Each rngMyCell In .Range(strMyCol & lngStartRow & ":" & strMyCol & Cells(.Rows.count, strMyCol).End(xlUp).row)
                If CVar(rngMyCell.offset(1, 0)) <> varMyValue Then
                    Exit For
                End If
            Next rngMyCell
        End With
        
        Application.ScreenUpdating = True
        MsgBox rngMyCell.row

Line in red leaving me with an "Object variable or With Block variable not set"

 

[Ark68]

Ooops. Found my mistake!

For Each rngMyCell In .Range(strMyCol & lngStartRow & ":" & strMyCol & .Cells(.Rows.count, strMyCol).End(xlUp).row)

 

[Trebor76]

I'm glad we got it all sorted in the end

Thanks for the thanks and the like