Tom’s Tutorials For Excel: Filtering Columns

Tom’s Tutorials For Excel: Filtering Columns
Free and safe downloadable workbook to filter by columns

As you know, Excel only provides a built-in method of filtering for rows by individual columns.

If you want to filter columns by their row headers, here are two methods: one with Data Validation and the other with a UserForm interface. The code is in this free workbook example.

There are two worksheets in the downloadable workbook. One sheet is named Filter Columns Data Validation, where drop-down lists in column A contain the items for each row.



For example, May is selected in A2, which filters columns to show cells in row 2 that contain May.



The other sheet in the downloadable workbook is named Filter columns with userform. As directed by the pop-up screen tip, double-click a row header cell in column A to call the userform.



In this example, cells along row 4 are filtered that only contain “Sat”.


The filtered result for row 4, exposing only columns whose cells contain “Sat”.



In this example, the more advanced filter options are utilized. Here, row 4 is filtered for items less than the letter S.



The filtered result for row 4, exposing only columns whose cells contain items alphabetically less than the letter S.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
26 comments on “Tom’s Tutorials For Excel: Filtering Columns
  1. Rajesh Sinha says:

    Plz mail me All Workbooks related to Filter data by column since current link is not working,, thanks.

    • Tom Urtis says:

      The current link does work and always did work. I just tested it again, no problem. Try a different browser or make sure your browser allows for files to be downloaded and opened.

  2. A says:

    Tom, it’s excellent! Thank you very much!

  3. A says:

    Tom, maybe you could advise how to fix one small thing. I put it into my file and I use Second option – Filter columns with user form, which is far more advanced. However, it filters only till Column T, the rest of columns remain unaffected. I tried to quickly change the value of variable LastColumn in the 7th row of Sheet2 Module and insetad of LastColumn = Cells(Target.Row, Cells.Columns.Count).End(xlToLeft).Column place LastColumn = 200 but it does not help.
    Can you suggest what to do to make sure it handles all columns? My last column is DS.
    Thank you!
    Art

  4. A says:

    I noticed that it only happens before applying your filter I filter the A column first to show only the values I need in this column. When the column remains unfiltered, your filter applies to all fields up to DS. Do you know how to make sure that it works with pre-filtered column as well?
    Thanks!

  5. A says:

    OK, looks like I found a workaround. I first apply your row filter to make sure it works for all the columns and then I apply Excel column filters. Works just fine.
    Thanks again for a great module!

  6. Grace says:

    OK…I downloaded the workbook. Awesome. But where do I find ¨the code¨ or the instructions to actually do it? I want to try the Data Validation method.

    • Tom Urtis says:

      The code is already set up for you to test it. Each worksheet module has its own event code which you can see by right-clicking the sheet tab, then left click on View Code, and you will see the code. While there in the Visual Basic Editor you can right-click onto the ThisWorkbook module and select View Code (or just double-click the ThisWorkbook module name in the Project window) to see the Open and BeforeClose events. I just checked, and all the code is in there, where it should be. Just follow the pop-up instructions as you select a cell in column A on the worksheets.

  7. TJ says:

    Hi,

    I like the userform column filter option. But after copying the code from your file into my worksheet, and right after I double click, I get the following error. How can I get this to work properly?

    Thanks in advance for your help.

    Compile error: variable not defined. And 2 lines of the code is highlighted as noted below

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) <<<<<< whole line is highlighted in yellow
    If Target.Column 1 Then Exit Sub
    If Len(Target.Value) = 0 Then Exit Sub
    If Len(Target.Value) > 0 And WorksheetFunction.CountA(Rows(Target.Row)) = 1 Then Exit Sub
    Cancel = True
    Cells.Columns.Hidden = False
    LastColumn = Cells(Target.Row, Cells.Columns.Count).End(xlToLeft).Column <<<<< ('LastColumn=' is highlighted in blue)
    FilterRow = Target.Row
    frmFilter.Show
    End Sub

    • Tom Urtis says:

      Hello TJ:

      It looks to me, based on what you posted here, that the reason for the errors you describe are due to the variables not being recognized. When you downloaded the workbook, take a look at the VBE and notice in Module1 are these 5 Public variables:
      Public LastColumn&
      Public cell As Range
      Public FilterRow&
      Public i%
      Public j%

      Stick those variables in a standard module for the workbook you are using, and you should be OK.

      • TJ says:

        Thanks for your response. All I did was copy the code into my sheet and did not change anything. In an running Excel 2016 which may be the issue.

        • Tom Urtis says:

          Version 2016 is not the problem. I asked you to check if you copied the variables from module1 of the downloaded workbook into a standard module of the workbook you are experiencing the errors. Did you?

          • TJ says:

            So I copied over the Module1 codes as well and I get the same error message but at the code/line below.

            Compile error: variable not defined.

            frmFilter.Show

            • Tom Urtis says:

              Your replies do not come with enough confirming detail. For example, you wrote:
              “But after copying the code from your file into my worksheet”
              and
              “All I did was copy the code into my sheet”

              What exactly does that mean? Do you mean the worksheet module? And what does “All I did was…”? If all you did was paste all the code including the variables into the worksheet module then the code might fail too.

              To put it another way, when you downloaded the original workbook (that I created) and tested it, if it worked for you there, then the way the code is in that workbook will work for you in your other workbook f you have it set up the same way.

  8. TJ says:

    I am not a VBA expert and it was not clear in the instructions above that all 3 ‘objects’ (the sheet code, Form – frmFilter, and Module1 must be copied/created in the new worksheet.

    Having done that, I got my worksheet filtering correctly. Thank you so much for your help. This is a major time saving tool since I have to filter on columns all the way out to column VH or more.

    Thanks!

  9. Xavier Mory says:

    Hi there,

    first of all, thank you for your code it is what I was looking for !
    I am specially looking for multiple column filter, enabling to filter line 1 AND line 2 for example or filter in one line by 2 items.

    Could you give me a hand doing this ?
    May I send you a simple example ?

    Best regards
    Xavier

  10. Xavier says:

    Hi there,

    first of all, thank you for your code it is what I was looking for !
    I am specially looking for multiple column filter, enabling to filter line 1 AND line 2 for example or filter in one line by 2 items.

    Could you give me a hand doing this ?
    May I send you a simple example ?

    Best regards
    Xavier

    • Tom Urtis says:

      If it were me, I would either write an ad hoc macro to do this, or if I had this situation arise in many workbooks and it was a common issue, I would amend the code in my workbook example with an And operator. I am confused about the “filter line 1 AND line 2” bit. Not asking for or wanting your example workbook for now; I prefer seeing if questions can be answered so everyone looking at this can also benefit.

  11. Xavier says:

    Dear Tom,

    thank you for your answer
    I basically would like two things:
    – display with the header 1 several items , in your example several names
    – have multiple filter: header 1 (by name) AND header 2 (by month) as example.

    I have tried to manipulate your example but I am not that good in writing in VBA so far.
    With pleasure I could send you my example workbook.

    Best regards
    Xavier

    • Tom Urtis says:

      If it were me, considering what you want to do, before I would expand my example, I would compose an event tied to the header cell’s selection, or a macro to accomplish this. I suggest that you ask for assistance with that on an excellent forum for Excel that is monitored by many experts around the clock. It’s free and easy to become a member, at mrexcel.com.

  12. Sebastien G. says:

    Hello,

    I downloaded the example and what I am trying to do is almost what we can see in the tab “Filter columns Data Validation”. However, what I see is that when I click on “Header 1” (or 2, 3, etc…), I can select only one values (for example “Bob” for “header 1”).

    Would it be possible to have something similar but instead of selecting “Bob” only, I could select “bob”, “Mike” and “Jim” and then see the corresponding columns?

    If possible, how ?

    Thanks in advance for your help on this.

    Best regards,
    Sebastien G.

    • Tom Urtis says:

      When I created this example, I more or less wanted to mimic the AutoFilter functionality. What you are asking for, which is do-able, is to approach the issue as if it were an Advanced Filter. Because your question is a one-off task, I would either build an interface with a ListBox for the user to multi-select the names to be filtered for, or on another sheet to make the job simpler but not as pretty, list the names to be filtered for on another sheet and loop through them to show only the columns in the primary sheet whose cells in the row of interest contain either of the names you want. For that solution, because I am tied up with my clients’ projects, I suggest you ask about it at a popular Excel forum that I also contribute to when I have the time, at https://www.mrexcel.com/board/forums/excel-questions.10/. It’s free and easy to become a member, with experts around the clock who can help you.

Leave a Reply

Your email address will not be published.

*