如何进行Selection.AutoFilter从第3行而不是第1行开始 [英] How to make Selection.AutoFilter starts in row 3 instead of row 1

问题描述

我有此代码:

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

仅当工作表control中的标题位于第1行时，它才能很好地工作.
我的问题是\\MyPath\Workbook.xlsx是只读文件，其标题从第3行开始.

It works well only if headers in the sheet control are located in row 1.
My problem is that \\MyPath\Workbook.xlsx is a read only file and its headers starts in row 3.

推荐答案

尝试以下代码:

• 避免在代码中使用选择".
• 在过程开始时关闭屏幕更新时，始终将Application.ScreenUpdating = True设置在底部.

• Avoid using Select in your code.
• Always set Application.ScreenUpdating = True at bottom when you turn off the screen updating at beignning of procedure.

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

这篇关于如何进行Selection.AutoFilter从第3行而不是第1行开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！