CODEWITHSUNDEEP
excelvba
Ryan Shannon
Ryan Shannon

Reputation: 710

Autofilter not working

I have a 13 row range with the first row a header row and the next 12 rows filled in by a QueryTable. The query will return at most 12 rows, but possibly less. Below this range I have a totals row.

I want to hide any rows that do not have data and I'm using AutoFilter to do this. When I run the code it creates the AutoFilter but doesn't apply the criteria. If I step through the code, it works just fine. Any ideas?

Sub fillTable()
    Dim strConn As String
    Dim strSQL As String
    Dim qt As QueryTable

    Sheet15.AutoFilterMode = False

    Sheet15.Range("DCRTable").ClearContents
    strConn = "ODBC;DSN=MS Access Database;DBQ=<db path>;"
    Set qt = Sheet15.QueryTables.Add(strConn, Sheet15.Range("DCRTable"))
    qt.CommandText = <sql query>
    qt.AdjustColumnWidth = False
    qt.EnableRefresh = False
    qt.FieldNames = False
    qt.Refresh

    hideEmpties Sheet15.Range("DCRTable").offset(-1).Resize(13)
End Sub

Sub hideEmpties(rng As Range)
    rng.Parent.AutoFilterMode = False
    With rng
        .AutoFilter
        .AutoFilter 1, "<>", , , False
    End With
End Sub

Upvotes: 1

Views: 3582

Answers (1)

MikeD
MikeD

Reputation: 8941

First of all, I cannot see how you assigned your sheet to object "Sheet15" - maybe this is part of the problem. If you have a named range, you don't need a Sheet object before that.

Secondly, .Autofilter with no arguments just toggles the Autofilter arrow on/off, and you loose your criteria.

Try this code, it worked for me (Office 2003) ... I have a header in A1, followed by A2..A13 named as DRCTable, followed by a =SUM(DRCTable) in A14

edit 02-Jan-2010

(sorry I can't test ODBC from my holiday domicile so this part is tested only syntactically) 

Sub fillTable()
Dim MySheet As Worksheet, MyRange As Range
Dim MyQRY As QueryTable, MyCONNECT As String, MySELECT As String ' added 02-Jan-2010

    'initialize
    Set MySheet = Sheets("Sheet1")
    Set MyRange = Range("DRCTable")

    MyCONNECT = "ODBC;..."           ' added 02-Jan-2010
    MySELECT = "SELECT * FROM DUAL"  ' added 02-Jan-2010 .. noticed my Oracle past :) ??

    Set MyQRY = ActiveSheet.QueryTables.Add(Connection:=MyCONNECT, _
                                            Destination:=MyRange, _
                                            Sql:=MySELECT)

    ' clean up
    MySheet.AutoFilterMode = False
    MyRange.ClearContents

    ' simulate Query      removed 02-Jan-2010
    ' MyRange(1, 1) = 1   removed 02-Jan-2010
    ' MyRange(2, 1) = 2   removed 02-Jan-2010
    ' MyRange(3, 1) = 3   removed 02-Jan-2010
    ' MyRange(4, 1) = 4   removed 02-Jan-2010
    ' MyRange(5, 1) = 5   removed 02-Jan-2010

    ' added 02-Jan-2010
    ' BackgroundQuery = FALSE waits until query is executed
    '                 = TRUE continues with VBA once connection to DB is made

    MyQRY.BackgroundQuery = False ' set this to affect all subsequet refresh
                                  ' and don't use the bool in refresh except
                                  ' to override the setting

    MyQRY.Refresh False ' the false sets BackgroundQuery for the individual refresh;
                        ' there is no default on this param within the refresh method.
                        ' If unspecified here the QueryTable.BackgroundQuery property
                        ' is examined

    ' select data range plus header and filter
    Range(MyRange.Offset(-1, 0), MyRange(12, 1)).AutoFilter _
        Field:=1, Criteria1:="<>"

End Sub

Hope that helps Good luck MikeD

Upvotes: 1

Related Questions