CODEWITHSUNDEEP
vbaexcelautofilter
Peter Mogford
Peter Mogford

Reputation: 496

Creating an auto filter code

Good Morning,

I am considerable new to VBA and was looking for some assistance on writing an auto-filter code for my table.

Tariffs     |   SME100  |   Enterprise  |   CustomerLoyalty |   AccountManage   
------------+-----------+---------------+-------------------+-------------------      
Voda Red    |   1       |     1         |         0         |          1
Voda 1G D   |   1       |     0         |         1         |          0

1* eligible to sell
0* not eligible sell

I am trying to write a code that takes the value from a validation box ("B2") and automatically filters the specific column of that sales channel for the eligible tariffs. My current code is:

Sub Filter()
    Dim strRange As String
    strRange = "B"
    Dim b As Integer
    b = "2"
    Range = ActiveSheet.Cells(2, 2).Address(False, False)

    If Range = True And Range = "SME100" Then
        ActiveSheet.ListObjects("TariffTable").Range.AutoFilter Field:=2, Criteria1:="1"

    If Range = True And Range = "Enterprise" Then
        ActiveSheet.ListObjects("TariffTable").Range.AutoFilter Field:=3, Criteria1:="1"

    If Range = True And Range = "CustomerLoyalty" Then
        ActiveSheet.ListObjects("TariffTable").Range.AutoFilter Field:=4, Criteria1:="1"

    If Range = True And Range = "AccountManagement" Then
        ActiveSheet.ListObjects("TariffTable").Range.AutoFilter Field:=5, Criteria1:="1"
    Else
        MsgBox ("No Sales Channel Selected")
    End If   
End Sub

Any advise will be much appreciated

Upvotes: 0

Views: 298

Answers (2)

Steven
Steven

Reputation: 781

I would approach it in a different way:

Sub Filter()

    Dim columnNumber, tableRow, tableColumn, tableWidth As Integer
    Dim tableName, columnName As String

    tableName = "Table1"
    columnName = ActiveSheet.range("B2").Value

    'This clears the existing filter
    ActiveSheet.ListObjects(tableName).range.AutoFilter

    'Assign some numbers we need to know about the table to check the headers
    tableRow = ActiveSheet.ListObjects(tableName).range.Row
    tableColumn = ActiveSheet.ListObjects(tableName).range.Column
    tableWidth = ActiveSheet.ListObjects(tableName).range.Columns.Count

    'If a column title with the specified value does not exist VBA throws an error which we need to catch, this is one of the many reasons I dislike VBA :D
    On Error GoTo ErrorHandler
    'Search through the table column header row to find the specified column and assign the number to columnNumber
    columnNumber = Application.WorksheetFunction.Match(columnName, range(Cells(tableRow, tableColumn), Cells(tableRow, tableColumn + tableWidth)), 0)

    'Apply the filter "1" to the found columnNumber
    ActiveSheet.ListObjects(tableName).range.AutoFilter Field:=columnNumber, Criteria1:="1"

    'Exit the sub otherwise the "error handling" will be provoked
    Exit Sub

ErrorHandler:

    MsgBox columnName & " does not exist"

End Sub

Edit: Plus you should read and understand sancho.s's answer.

Upvotes: 1

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

I suggest modifications, checks, etc.:

  1. You probably need Range = ActiveSheet.Cells(2, 2).Text (or using a different name, see below). This is likely the source of error. Plus, there is a lot to improve in your code.

  2. Use Dim colstr as String, colstr = ... instead of Range = ....

  3. Make sure TariffTable is correctly defined.

  4. AccountManagement should read AccountManage.

  5. Make sure ActiveSheet refers to the Sheet you want to work with.

  6. Inquire If colstr = "Enterprise" Then instead of If colstr = True And colstr = "Enterprise" Then (already using a changed name).

  7. You can improve over using multiple Ifs, e.g., with Select Case, or even matching colstr against the Range containing the headings.

PS: You did not post the output/errors of your code.

Upvotes: 0

Related Questions