vba 如何使 Selection.AutoFilter 从第 3 行而不是第 1 行开始
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16803299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make Selection.AutoFilter starts in row 3 instead of row 1
提问by phalanx
I have this code:
我有这个代码：
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Application.ScreenUpdating = False
If Target.Value <> "" Then
Set wbks = Workbooks.Open("\MyPath\Workbook.xlsx")
wbks.Sheets("Control").Activate
ActiveSheet.Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=Target.Value '7 is the filter # column
End If
End Sub
It works well only if headers in the sheet controlare located in row 1.
My problem is that \\MyPath\Workbook.xlsxis a read onlyfile and its headers starts in row 3.
只有当工作表control中的标题位于第 1 行时，它才能正常工作。
我的问题是这\\MyPath\Workbook.xlsx是一个只读文件，它的标题从第 3 行开始。
采纳答案by Santosh
Try below code :
试试下面的代码：
- Avoid using Select in your code.
- Always set
Application.ScreenUpdating = Trueat bottom when you turn off the screen updating at beignning of procedure.
- 避免在代码中使用 Select。
- 程序开始
Application.ScreenUpdating = True时关闭屏幕更新时，始终设置在底部。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Value <> "" Then
Set wbks = Workbooks.Open("\MyPath\Workbook.xlsx")
wbks.Sheets("Control").Activate
wbks.Sheets("Control").Range("A3:G3").AutoFilter Field:=7, Criteria1:=Target.Value '7 is the filter # column
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
回答by Newbie
Try addding this code before the autofilter
尝试在自动过滤器之前添加此代码
Rows("3:3").Select
Making the code:
制作代码：
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Application.ScreenUpdating = False
If Target.Value <> "" Then
Set wbks = Workbooks.Open("\MyPath\Workbook.xlsx")
wbks.Sheets("Control").Activate
ActiveSheet.Range("A1").Select
Rows("3:3").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=Target.Value '7 is the filter # column
End If
End Sub
Hope it helps, Bruno
希望它有所帮助，布鲁诺
