+ Reply to Thread
Results 1 to 9 of 9

Hide columns based on table filter

  1. 03-18-2020, 11:07 PM #1
    lucacarbi
    lucacarbi is offline
    Registered User
    Join Date
    03-18-2020
    Location
    Italy
    MS-Off Ver
    365
    Posts
    4

    Hide columns based on table filter

    Hi there! This is my first thread... congrats this forum is amazing!

    Here is my problem:
    I have a table with office departments in rows and PC software in columns.
    I filter by departement (i.e. HR) and i want to hide automatically some columns software that aren't interesting for HR.

    I made a subtotal linked to the filter: when i want to hide columns, subtotal = 1.

    PROBLEM: to hide columns I’m using a simple macro (if subtotal = 1-->Hide) that runs only if i click on a random cell after filtering.
    Is it possibile to automate it bypassing the mouse click?
    Filter and immediatly the macro runs, I mean the macro is triggered by the filter and not by filter+random click in the sheet.

    My macro:
    Please Login or Register  to view this content.
    Thank you for your help!

    Luca
    Attached Files Attached Files
    Last edited by lucacarbi; 03-19-2020 at 05:32 AM. Reason: Update
    Reply With QuoteRegister To Reply
  2. 03-23-2020, 11:30 AM #2
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hide columns based on table filter

    Hi Luca,

    Try using just the change event:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  3. 03-23-2020, 11:52 AM #3
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Hide columns based on table filter


    Hi Luca !

    First : Forza Italia ‼ May be the force with you ! ♫ Volare ♪

    Your need could be automaticly done under VBA via a button using an advanced filter rather than a classic filter …
    Reply With QuoteRegister To Reply
  4. 03-23-2020, 03:41 PM #4
    lucacarbi
    lucacarbi is offline
    Registered User
    Join Date
    03-18-2020
    Location
    Italy
    MS-Off Ver
    365
    Posts
    4

    Re: Hide columns based on table filter

    Hi xladept,

    thanks for your answer. Sadly, I tried several times the Change event but it doesn't work.

    Anyway, I fix temporary the problem in this way:
    I created several button, and each button runs a recorded macro that filters rows and hide columns.
    It's not smart, but it works.

    I'm still wondering which is the macro that answers my original question...even the Calculate event doesn't work.
    Reply With QuoteRegister To Reply
  5. 03-23-2020, 03:48 PM #5
    lucacarbi
    lucacarbi is offline
    Registered User
    Join Date
    03-18-2020
    Location
    Italy
    MS-Off Ver
    365
    Posts
    4

    Re: Hide columns based on table filter

    Quote Originally Posted by Marc L View Post

    Hi Luca !

    First : Forza Italia ‼ May be the force with you ! ♫ Volare ♪

    Your need could be automaticly done under VBA via a button using an advanced filter rather than a classic filter …
    Hi Marc,

    thanks for your message.

    can you post a code as example?

    By the way, as I wrote to xladept, I fix temporary the problem.
    But my curiosity is still looking for a solution :D
    Reply With QuoteRegister To Reply
  6. 03-23-2020, 07:03 PM #6
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Hide columns based on table filter

    Hi Luca,

    Sorry the change event didn't work but glad you found a solution. I was unable to test your filter but when I changed it manually or with a macro that change event worked. How are you loading that field?
    Reply With QuoteRegister To Reply
  7. 03-23-2020, 11:41 PM #7
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !

    Quote Originally Posted by lucacarbi View Post
    can you post a code as example?
    As I didn't see you use an Excel table so with existing filter feature then for the next demonstration
    - you must first paste it to the Foglio1 (Richieste) worksheet module -
    just enter the department in cell A2 without the need of a button :

    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
        If     Target.Address = "$A$2" Then
                If Target.Text = "" Then
                Me    .ListObjects(1).Range.AutoFilter 1
                Columns    ("E:J").Hidden = False
                Else
                    Me.ListObjects(1).Range.AutoFilter 1, Target.Text
                Columns    ("E:J").Hidden = [N55] = 1
            End     If
            End If
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
    Reply With QuoteRegister To Reply
  8. 03-24-2020, 05:05 AM #8
    lucacarbi
    lucacarbi is offline
    Registered User
    Join Date
    03-18-2020
    Location
    Italy
    MS-Off Ver
    365
    Posts
    4

    Re: Try this !

    Thnk you, interesting code.

    I put in A2 a department list so that I can choose from a drop down menu. It works great!
    Last edited by lucacarbi; 03-24-2020 at 05:07 AM.
    Reply With QuoteRegister To Reply
  9. 03-24-2020, 07:11 AM #9
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Hide columns based on table filter


    Well done and thanks for the rep' !

    Take care …

    ♫ Volare, ♪ Cantare ♫
    ♪ Nel blu dipinto di blu
    ♫ Felice di stare lassù ♪
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Inserting filter into pivot table via macro errors if filter doesn't exist.
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2018, 10:24 AM
  2. Need help immediatly
    By faizansaigol in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2016, 01:32 PM
  3. Issues with Macro to import data from web, create pivot table & filter it based on a dynam
    By Anikipipo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-12-2014, 01:16 AM
  4. filter based on the previous filter in excel - using pivot table / macros
    By vinsocf in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-07-2014, 03:12 AM
  5. filter based on the previous filter in excel - using pivot table / macros
    By vinsocf in forum Excel General
    Replies: 1
    Last Post: 02-07-2014, 03:04 AM
  6. Macro to Filter Table Based on Contents of a Cell which is Text
    By jgray in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2013, 11:57 PM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1