Results 1 to 7 of 7

Setting Filters in different rows automatically

Threaded View

  1. #1
    Registered User
    Join Date
    04-10-2015
    Location
    FRA
    MS-Off Ver
    2010
    Posts
    20

    Setting Filters in different rows automatically

    Dear All,

    I am looking for Code which sets a filter depending on the value of cell D2 in different columns.

    If D2 equals All no filter is set or a set filter is cleared.
    IF D2 equals A the filter in column D is set to 1.
    IF D2 equals B the filter in column E is set to 1.
    ...
    IF D2 equals G the filter in column I is set to 1. If D2 contained a letter beforehand the filter of the respective column has to be cleared. Only the filter in column I can be set.

    I have an idea how to set a filter in one column. But in this case I need the code to change the filtered column depending on the value. Also I have the problem that the code below is fixed to Table1. Since the worksheet will be copied I have the problem that the reference to the new table needs to modified automatically. Is it possible to automate it?
    Another problem is that the code only accepts numbers for the different cases and no letters.
    Finally the code produces errors whenever I add a new row or change the content of a cell. Why is this happening?

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lSelection As Long, stFilter As String
        If Target = Range("D2") Then
            lSelection = Range("D2")
    
            Select Case lSelection
            Case Is = All [comment: accepts no letters]
                stFilter = "*"
    
            Case Is = A [comment: accepts no letters]
                stFilter = "1"
    
            Case Is = B [comment: accepts no letters]
                stFilter = "1"
    
            Case Else
                'default code
    
            End Select
            
            ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=stFilter
        End If
    End Sub
    Please find the example attached.

    Many thanks.

    Best regards,
    fxmu
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Reapply Filters for MULTIPLE tables Automatically
    By CrazyCookie in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-21-2016, 07:30 PM
  2. Why does this code freeze Excel? Help to automatically re-apply auto filters?
    By zt001 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2015, 10:41 AM
  3. How can I automatically refresh auto filters?
    By DanielWinning in forum Excel General
    Replies: 5
    Last Post: 08-20-2013, 11:20 AM
  4. Button or tick boxes setting filters
    By D-smoke in forum Excel General
    Replies: 16
    Last Post: 07-26-2012, 02:14 PM
  5. Automatically refresh Auto Filters
    By MysticGenius in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-04-2012, 02:05 AM
  6. Replies: 1
    Last Post: 10-12-2009, 09:14 AM
  7. Setting up multiple filters
    By rkrause in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-08-2007, 10:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1