Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 425
phalanx Avatar asked May 28 '13 23:05

phalanx


2 Answers

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
like image 105
Santosh Avatar answered Nov 15 '22 08:11

Santosh


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

like image 36
Newbie Avatar answered Nov 15 '22 08:11

Newbie