在excel vba中搜索多个列 [英] Searching over multiple columns in excel vba

问题描述

我可以使用这个

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

之后，我可以通过执行 FindRow.Row

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

然后如何返回行号，其中列A ==ProjTemp1&&列B ==ProjTemp2&&列C ==ProjTemp3

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

推荐答案

尝试使用自动过滤器：

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'

这篇关于在excel vba中搜索多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！