Filtering Horizontally

[Beanybala]

Dear all

I have seen before on a spreadsheet that People were able to filter data horizontally with the Filter button.

I have to use my data horizontally for a reason, however there are 4 sets of large data ranged from 2013, 2014 2015 and 2016.

Are there any easy options to hide 2013 or 2014 with a click, preferably with the filter button?

Or do I have to resort to Macro

Best Regards

Thank you

 

[Fishboy]

Dear all

I have seen before on a spreadsheet that People were able to filter data horizontally with the Filter button.

I have to use my data horizontally for a reason, however there are 4 sets of large data ranged from 2013, 2014 2015 and 2016.

Are there any easy options to hide 2013 or 2014 with a click, preferably with the filter button?

Or do I have to resort to Macro

Best Regards

Thank you

Hi Beanybala,

This could be achieved with some relatively simple VBA if that is an option?

In my test example I made cell A1 the "Filter Value" and then had a load of different years spread out across row 1 from B1 to Z1 (it could be as wide as you need).
In A2 I put a control button to run the following macro:

Sub Makeshift_Horizontal_Filter()
' Defines variables
Dim Cell As Range, cRange As Range
    ' Disable screen updating to reduce flicker
    Application.ScreenUpdating = False
        ' Define LastCol as the last column of data based on values in row 1
        LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
            ' Set the check range as B1 to the last column of data on row 1
            Set cRange = Range("B1", Cells(1, LastCol))
                ' If A1 has a value in it then...
                If Range("A1").Value <> "" Then
                    ' For each cell in the check range
                    For Each Cell In cRange
                        ' If the cell value does not match the value from A1 then...
                        If Cell.Value <> Range("A1").Value Then
                            ' Hide the entire column
                            Cell.EntireColumn.Hidden = True
                        ' Else if the cell value matches A1 then...
                        Else
                            ' Unhide the entire column
                            Cell.EntireColumn.Hidden = False
                        End If
                    ' Check next cell in check range
                    Next Cell
                ' If A1 is blank then...
                Else
                    ' For each cell in the check range
                    For Each Cell In cRange
                        ' Make sure all columns are not hidden
                        Cell.EntireColumn.Hidden = False
                    ' Check next cell in check range
                    Next Cell
                End If
    ' Re-enables screen updating
    Application.ScreenUpdating = True
End Sub

 

[Beanybala]

Hi, Thank you very much for the code,
Its working well! but its only hiding the first column in Column B2. I am not sure what a Control Button is, so I just inserted an object in B2 and assigned it with Macro.

Sorry to fiddle around with you, when users put in 2013 year, it hides 2013, can it be changed that when users put in 2013, columns that contains other years will be hidden?

Thank you

 

[Fishboy]

Hi, Thank you very much for the code,
Its working well! but its only hiding the first column in Column B2. I am not sure what a Control Button is, so I just inserted an object in B2 and assigned it with Macro.

Sorry to fiddle around with you, when users put in 2013 year, it hides 2013, can it be changed that when users put in 2013, columns that contains other years will be hidden?

Thank you

That is what it should already be doing.

I have had to tweak the code slightly as it didn't seem to be "unhiding" correctly if A1 was blank when the button was pressed. I have also had to manually specify the last column for the unhiding so you may need to amend the bold red AG in the code below to suit your real data.

Sub Makeshift_Horizontal_Filter()
' Defines variables
Dim Cell As Range, cRange As Range
   ' Disable screen updating to reduce flicker
    Application.ScreenUpdating = False
       ' Define LastCol as the last column of data based on values in row 1
        LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
           ' Set the check range as B1 to the last column of data on row 1
            Set cRange = Range("B1", Cells(1, LastCol))
               ' If A1 has a value in it then...
                If Range("A1").Value <> "" Then
                   ' For each cell in the check range
                    For Each Cell In cRange
                       ' If the cell value does not match the value from A1 then...
                        If Cell.Value <> Range("A1").Value Then
                           ' Hide the entire column
                            Cell.EntireColumn.Hidden = True
                       ' Else if the cell value matches A1 then...
                        Else
                           ' Unhide the entire column
                            Cell.EntireColumn.Hidden = False
                        End If
                   ' Check next cell in check range
                    Next Cell
                ' If A1 is blank then...
                ElseIf Range("A1").Value = "" Then
                        ' Make sure all columns are not hidden
                        Columns("B:AG").Hidden = False
                End If
   ' Re-enables screen updating
    Application.ScreenUpdating = True
End Sub

I have uploaded my sample workbook here for you to see it in action as intended.

For the record, to add a button you just go to the Developer tab on the Excel ribbon at the top of the screen, click Insert, then click the first option in the list (Button: Form Control). If you already have macros in your workbook you will get the option to pick one to assign to the button. If you have not got any macros yet it will give you the chance to write / record one.