CODEWITHSUNDEEP
excelautofiltervba
phalanx
phalanx

Reputation: 497

How to make Selection.AutoFilter starts in row 3 instead of row 1

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 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.

Upvotes: 6

Views: 28809

Answers (2)

Santosh
Santosh

Reputation: 12353

Try below code :

  • 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

Upvotes: 6

Newbie
Newbie

Reputation: 873

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

Upvotes: 1

Related Questions