+ Reply to Thread
Results 1 to 17 of 17

Columns Slicer for dummies. Please help with code

  1. #1
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Columns Slicer for dummies. Please help with code

    I'm Macro dummie and don't know much about macros, so Guru's please help!!!

    I need to have filters vertically and horizontally and I have done it via Slicers and followed the steps from here excelcampus com / hide-unhide-columns-slicer-filter (sorry not allowed to post the link)

    It gives me an error and I probably have done something very silly as I do not know much about codes and macros but that is why I'm looking for help.

    This is sample document and I will be repeating the work on the original database, so I would like to understand what I'm doing incorrectly.

    Thank you very much in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Columns Slicer for dummies. Please help with code

    You're referring to named ranges that do not exist so how do you want anyone here to help you.
    Guessing what you want is no option.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Columns Slicer for dummies. Please help with code

    Quote Originally Posted by ELFIJKA View Post
    I need to have filters vertically and horizontally and I have done it via Slicers and followed the steps from here excelcampus com / hide-unhide-columns-slicer-filter (sorry not allowed to post the link)
    Based on a quick review of the instructions at that site (link here) and your workbook, there are quite a few steps that you did not follow. They offer a free video series on macros and VBA, which they recommend if you're not familiar with modifying macros.

  4. #4
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Re: Columns Slicer for dummies. Please help with code

    Now I see that I should have changed the headings for the file, tabs and filters. I have never used the macros before and this is way too advanced for me...

    May be there is an easier way to do filters vertically? I have this idea like on the picture below but can't find the way to make it work... IE dropdown on E2 cell with selection on the row (Apples, Carrot, Pear) and once you tick say Pear and Carrot it would hide columns all other columns...

    Vertical Filter Idea.jpg
    Last edited by ELFIJKA; 03-14-2019 at 05:07 AM.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Columns Slicer for dummies. Please help with code

    Like I said there is no structure to your table
    Where do you reference Apple or Pear to the tabel?
    There is no way to see if the row in the table refers to Apples or Pear or whatever.

  6. #6
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Re: Columns Slicer for dummies. Please help with code

    May be I just can't explain it right... There is perfect structure on the spreadsheet and it's huge. Apples / Pears are the type of stock and are in Row 2

    Example cell I6
    PP/5.3/BB - is stock code of product we sell and it's unique. This stock code is always a Fruit and Pear and Packaged in Cage (rows 1-3). If we sell PP/5.3/BB to John in Limerick store between 13/03/2019 - 31/12/2019 on Half truck rate we charge 10.94 (Cell I9)

    Now imagine that we have 1500 various stock codes going across the table but in Row 1 they are only Fruit/Veg/Berries/Root, in Row 2 there could be 35 different variations (Apple/Pear/Root/Veg etc)

    Now imagine Customer is ringing to place an order:

    John from Limerick want to order Pears. We filter in Column A - John, Column B - Limerick. Now the funny part there are 5 types of pears (5 stock codes) - they are located anywhere across 1500 columns. If I could make excel hide all columns that do not contain Pear in Row 2 then I would be left only with 5 columns and 3 rows to choose the price from...

    Does it make sense what I'm trying to achieve?

    Thanks a lot for helping.
    Last edited by ELFIJKA; 03-14-2019 at 06:26 AM.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Columns Slicer for dummies. Please help with code

    In some way it makes sense but I do not see the relationship between Apples and / or Pears etc the columns of your table.

    I added the Pivot table (nothing about Apples etc because they're not (indetifiable) in the table
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Columns Slicer for dummies. Please help with code

    What it comes down to is there is no easy / built-in way to use filters to hide columns. It will require VBA. Excel's data structure just isn't designed that way.

  9. #9
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Re: Columns Slicer for dummies. Please help with code

    Thanks a lot for the file - I did look on that. We are already doing that just in a different format. I have added a raw format document here, may be this way it will be clearer.

    The relationship of Apple/Pear is to the stockcode - that is why it's on the top of the stock code and this is where I need help. If you look on the file attached here Rows 1 to 3 are repetitive and they are related to a stock code.

    I want to have selection in Row 2 from Apple/Pear/Carrot etc, once I make selection IE Apple it would hide columns I; J; K; M; N

    Does it make sense?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Re: Columns Slicer for dummies. Please help with code

    Melvosh - Yea this is what I have discovered after spending full day searching for easy solution. In some examples I have seen Pivot filtering columns. But I still can't figure out how to achieve it...

    I have found example with Slicers (link is above) and trying to follow the steps but when it comes to the code I'm lost...

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Columns Slicer for dummies. Please help with code

    And again. How difficult is to it to EXPLAIN what I have been asking?
    See the yellow block in the attached screen shot.
    Attached Images Attached Images

  12. #12
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Columns Slicer for dummies. Please help with code

    Keebellah, I think that whatever values for Stock Category, Type, Loading are in a column apply to that column, e.g. in column F, AA/5.3/BB is a Fruit, specifically Apples, shipped in a Box. I'm not 100% sure though. It's not the way I would have set up data in Excel, but it's not my data.

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Columns Slicer for dummies. Please help with code

    Well if you are not even 100% sure how to you want anybody else to help you with something like that.
    In my opinion this will only work using VBA and very important knowing EXACTLY what to do, but there even you are not sure.
    I don't work with 'guessing' or assumptions, but I'm somebody else might like the challenge.

  14. #14
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Columns Slicer for dummies. Please help with code

    It's not my thread, I've just been keeping an eye on this thread. And I agree with you, this can only be done with VBA. I just based my guess on the data structure off a previous explanation from ELFIJKA

  15. #15
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Re: Columns Slicer for dummies. Please help with code

    Yes Melvosh is right - this is the data applicable to each column.

    What way do you suggest to set up the data? May be there is better way but I just don't know that...

    Thanks

  16. #16
    Registered User
    Join Date
    10-04-2017
    Location
    Dublin
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    34

    Re: Columns Slicer for dummies. Please help with code

    I have just finished the project and it works. The solution I have used can be found here https://www.excelcampus.com/vba/hide...slicer-filter/

    There is no way to avoid Macros in order to achieve what I was looking for, but I must admit they are not so complex.

    The result is attached and this is exactly what I was looking for.

    I would like to thank you everyone for your help and assistance!
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Columns Slicer for dummies. Please help with code

    Glad it worked out for you, and congrats on implementing the macros!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Table Slicer for Columns
    By hansaaa in forum Excel General
    Replies: 3
    Last Post: 05-10-2018, 12:55 AM
  2. Loop through slicer, while also selecting single slicer value on separate slicer
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2018, 01:41 PM
  3. Replies: 1
    Last Post: 04-23-2018, 09:43 AM
  4. [SOLVED] Use Slicer to hide columns in table
    By David_90 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2018, 11:41 AM
  5. Changing Slicer value will run Code
    By hines57 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2015, 05:45 PM
  6. Troubles converting excel function into a usable VBA code (for dummies)
    By cam1212 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2015, 05:36 PM
  7. [SOLVED] Change Chart Title Depending on a Slicer - slicer advice
    By JungleJme in forum Excel General
    Replies: 8
    Last Post: 08-17-2012, 07:59 AM

Tags for this Thread

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