Find last row - conditionally

[AlexanderBB]

Google seems to have everything but !
Is there a good way to find the row number for the last item in "A" column, where item is a specific thing.
e.g. Row number for the last row where Col A = 3?

Or, if easier can you "get" a range ? e.g. all cells = "3" in Col "C" ?
I need to calculate this in Form code, not mousing on worksheet.

Thanks for any help.

 

[Rick Rothstein]

Is there a good way to find the row number for the last item in "A" column, where item is a specific thing.
e.g. Row number for the last row where Col A = 3?

Give this a try..

[table="width: 500"]
[tr]
	[td]LastRowWith[B][COLOR="#FF0000"]3[/COLOR][/B]inColumn[B][COLOR="#0000FF"]A[/COLOR][/B] = Columns("[B][COLOR="#0000FF"]A[/COLOR][/B]").Find([B][COLOR="#FF0000"]3[/COLOR][/B], , xlValues, , xlRows, xlPrevious, , , False).Row[/td]
[/tr]
[/table]

 

[AlexanderBB]

Many thanks Rick, that works spot on! With that I thought I could figure out the next bit...but alas...

I need to get the row before that which contain s "001". Please ??

 

[Rick Rothstein]

Many thanks Rick, that works spot on! With that I thought I could figure out the next bit...but alas...

I need to get the row before that which contain s "001". Please ??

[table="width: 500"]
[tr]
	[td][B][COLOR="#0000FF"]LastRowWith3inColumnA[/COLOR][/B] = Columns("A").Find(3, , xlValues, , xlRows, xlPrevious, , , False).Row
Prior001 = Range("A1:A" & [B][COLOR="#0000FF"]LastRowWith3inColumnA[/COLOR][/B]).Find("001", , xlValues, , xlRows, xlPrevious, , , False).Row[/td]
[/tr]
[/table]

 

[AlexanderBB]

Thanks very much Rick, both function working nicely.

 

[AlexanderBB]

Rick, with code in Msg2 looking for something that doesn't exist returns error 91 "Object Variable or With Block variable not set"
Apart from trapping this error, is there any change that would avoid that?
Thanks.

 

[Rick Rothstein]

Rick, with code in Msg2 looking for something that doesn't exist returns error 91 "Object Variable or With Block variable not set"
Apart from trapping this error, is there any change that would avoid that?

You can set a Range variable to the "found" cell... if it exists, then you can retrieve its row number. Something like this...

[table="width: 500"]
[tr]
	[td]Sub Test()

  Dim LastCellWith3inColumnA As Range, Prior001 As Long, LastRowWith3inColumnA As Long
  
  Set LastCellWith3inColumnA = Columns("A").Find(3, , xlValues, , xlRows, xlPrevious, , , False)
  
  If Not LastCellWith3inColumnA Is Nothing Then
    LastRowWith3inColumnA = LastCellWith3inColumnA.Row
    Prior001 = Range("A1:A" & LastRowWith3inColumnA).Find("001", , xlValues, , xlRows, xlPrevious, , , False).Row
  End If

End Sub[/td]
[/tr]
[/table]