Excel VBA检查自动过滤器的数据 [英] Excel VBA to check autofilter for data
问题描述
我需要帮助检查不包括标题的自动筛选的行。我想给它一个消息框没有找到记录。然后退出sub或继续复制粘贴,如果有行超出标题行。我知道过滤器后需要一个If / Else条目来检查数据,但我无法确定如何检查。这个代码是从我创建的UserForm按钮完成的。
I need help checking for autofiltered rows not including the header. I want it to give a message box "No records found." then exit sub or continue with copy paste if there are rows beyond the header row. I know I need an If/Else entry after the filter to check for data but I'm having trouble figuring how to check. This code is being done from a UserForm button I created.
这是我的脚本:
Private Sub Searchbycompanyfield_Click()
If CompanyComboBox1.Value = "" Then
MsgBox "Please enter a Company to begin search."
Exit Sub
End If
ActiveSheet.Range("$A:$H").AutoFilter Field:=1, Criteria1:=EQDataEntry.CompanyComboBox1.Value, Operator:=xlOr
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
ActiveSheet.Paste
Call MessageBoxYesOrNoMsgBox
End Sub
任何帮助将不胜感激。
推荐答案
参见下文,SpecialCells(xlCellTypeVisible)将允许您返回已过滤单元格的rng对象。您只需要检查这个条件的行数:
See below, SpecialCells(xlCellTypeVisible) will allow you to return a rng object of the filtered cells. You just need to check the number of rows in this for your condition:
Private Sub Searchbycompanyfield_Click()
If CompanyComboBox1.Value = "" Then
MsgBox "Please enter a Company to begin search."
Exit Sub
End If
Dim sh As Worksheet
Dim rng As Range
Set sh = ActiveSheet
sh.AutoFilterMode = False
sh.Range("$A:$H").AutoFilter Field:=1, Criteria1:=EQDataEntry.CompanyComboBox1.Value, Operator:=xlOr
Set rng = sh.UsedRange.SpecialCells(xlCellTypeVisible)
If (rng.Rows.Count > 1) Then
rng.Copy Sheets("Sheet2").[A5]
Call MessageBoxYesOrNoMsgBox
End If
End Sub
这篇关于Excel VBA检查自动过滤器的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!