MatchLastIf

Finds last occurrence of a match in a column
Finds the row of last time an item found in a column
That is until a blank space is found
Uses Range().End, not the best way, but works the fastest
Been thinking about this one for a while now, and needed it a lot across my 22 years with Excel-VBA.
Works exactly like MatchIf and VMatchIf, just finds the last item in a column.
Should be working with no issues in xls or xlsx workbooks (65k or 1m rows)
It is actually starts searching from last row (1m in xlsx or 65k in xls) then goes up until a non-blank cell found, then loop through that column until a first match is found for that item we are looking for

Function MatchLastIf(Val1, Col1, Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1)
MatchLastIf = VMatchLastIf(Val1, Col1, Wb, Shee, StartFromRow)
End Function
Function VMatchLastIf(Val1, Col1, Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1)
If Wb = "This" Then Wb = ThisWorkbook.Name
If Wb = "Active" Then Wb = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
VMatchLastIf = 0
Col1End = Col1 & Workbooks(Wb).Worksheets(Shee).Range("A1").EntireColumn.Rows.Count - 1
If WorksheetFunction.CountIf(Workbooks(Wb).Worksheets(Shee).Range(Col1 & StartFromRow, Col1End), Val1) = 0 Then Exit Function
LastRR = Workbooks(Wb).Worksheets(Shee).Range(Col1End).End(xlUp).Row
LastOne = ""
For I = LastRR To 1 Step -1
Val_i = Workbooks(Wb).Worksheets(Shee).Range(Col1 & I).Value
If Val_i = Val1 Or Val(Val_i) = Val(Val1) Or CStr(Val_i) = CStr(Val1) Then
LastOne = Workbooks(Wb).Worksheets(Shee).Range(Col1 & I).Row
Exit For
End If
Next
VMatchLastIf = LastOne
End Function

Val1, Col1, Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1

Views 305 Downloads 106

'Matchif', 'matchlastif', 'matchif_last', 'vmatchif', 'vmatchlastif', 'vmatchif_last', 'match', 'countif', 'lookup', 'vlookup', 'search', 'find', 'last', 'range', 'entirecolumn', 'end', 'xlup', 'xldown'

ANmarAmdeen
555
Attachments
Components VBA-Excel
Revisions

v2.0