Searching over multiple columns in excel vba

Question

I am able to search a text in column A of my spreadsheet by using this

With WB.Sheets("MySheet")
    Set FindRow = .Range("A:A").Find(What:="ProjTemp1", LookIn:=xlValues)
End With

After which I can get the row number by doing FindRow.Row

How do I then get back the row number where Column A == "ProjTemp1" && Column B == "ProjTemp2" && Column C == "ProjTemp3"

Answer 1

Try to use Autofilter:

Dim rng As Range
'disable autofilter in case it's already enabled'
WB.Sheets("MySheet").AutoFilterMode = False 

With WB.Sheets("MySheet").Range("A1:C1")
    'set autofilter'
    .AutoFilter Field:=1, Criteria1:="=ProjTemp1"
    .AutoFilter Field:=2, Criteria1:="=ProjTemp2"
    .AutoFilter Field:=3, Criteria1:="=ProjTemp3"
End With

With WB.Sheets("MySheet")
    On Error Resume Next
    Set rng = .Range("A2:A" & .Rows.Count).Rows.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
End With
If Not rng Is Nothing Then
   MsgBox rng.Row ' returns first visible row number
End If

WB.Sheets("MySheet").AutoFilterMode = False 'disable autofilter'