Hide\UnHide columns - automatically base on cell value

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
17
Hi guys,

i've been looking, in forum, for some related request and i've not found anything that fulfill my intentions.

If i am mistaken, please accept my apologies :(

what i'm trying to do is something like this.

Have a 30, consecutive, columns with date and i'm trying to figure it out a way to hide and unhide automatically the columns that i want base on a 6 months period of time.

i attached a file with my example to show with more detail my problem.
https://file.town/download/ipd8p42kqe3z59kpkippt6e24


It's possible to do that with a click button or check box? Filtering the columns that i want to show?

Kind regards to all members,
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Put a checkbox on your sheet and add the following code


Code:
Private Sub CheckBox1_Click()
    Dim uc As Long, i As Long
    uc = Cells(2, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, "D"), Cells(1, uc)).EntireColumn.Hidden = False
    If CheckBox1 = True Then
        For i = Columns("D").Column To uc
            If Cells(2, i).Value >= Range("B6").Value And Cells(2, i).Value <= Range("B7").Value Then
                Columns(i).Hidden = True
            End If
        Next
    End If
End Sub



I return the file with the check and the code

https://www.dropbox.com/s/8v71yovvvuhot42/EXAMPLE1 dam.xlsm?dl=0
 

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
17
Put a checkbox on your sheet and add the following code


Code:
Private Sub CheckBox1_Click()
    Dim uc As Long, i As Long
    uc = Cells(2, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, "D"), Cells(1, uc)).EntireColumn.Hidden = False
    If CheckBox1 = True Then
        For i = Columns("D").Column To uc
            If Cells(2, i).Value >= Range("B6").Value And Cells(2, i).Value <= Range("B7").Value Then
                Columns(i).Hidden = True
            End If
        Next
    End If
End Sub



I return the file with the check and the code

https://www.dropbox.com/s/8v71yovvvuhot42/EXAMPLE1 dam.xlsm?dl=0

Hi DanteAmor,

thanks for your reply. Although, i think i don´t explain myself clearly, and for that i'm sorry.

The exercise is just fine, but i want the opposite result (my mistake :()

Imagine this:

I am at Jan'19 ok? So i just want the period of time (columns visible) from period August'18 to January'19 (6 months range). Next month i'll be at February'19, so the columns that supposed to be visible are September'18 to February'19 and so on.

Kind regards for your help and availability

André
 

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
17
Put a checkbox on your sheet and add the following code


Code:
Private Sub CheckBox1_Click()
    Dim uc As Long, i As Long
    uc = Cells(2, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, "D"), Cells(1, uc)).EntireColumn.Hidden = False
    If CheckBox1 = True Then
        For i = Columns("D").Column To uc
            If Cells(2, i).Value >= Range("B6").Value And Cells(2, i).Value <= Range("B7").Value Then
                Columns(i).Hidden = True
            End If
        Next
    End If
End Sub



I return the file with the check and the code

https://www.dropbox.com/s/8v71yovvvuhot42/EXAMPLE1 dam.xlsm?dl=0

Hi DanteAmor,

thanks for your reply. Although, i think i didn´t explain myself clearly, and for that i'm sorry.

The exercise is just fine, but i want the opposite result (my mistake :()

Imagine this:

I am at Jan'19 ok? So i just want the period of time (columns visible) from period August'18 to January'19 (6 months range). Next month i'll be at February'19, so the columns that supposed to be visible are September'18 to February'19 and so on.

Kind regards for your help and availability

André
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Try:

Code:
Private Sub CheckBox1_Click()
    Dim uc As Long, i As Long
    Application.ScreenUpdating = False
    uc = Cells(2, Columns.Count).End(xlToLeft).Column
    Cells.EntireColumn.Hidden = False
    If CheckBox1 = True Then
        For i = Columns("D").Column To uc
            If Not (Cells(2, i).Value >= Range("B6").Value And Cells(2, i).Value <= Range("B7").Value) Then
                Columns(i).Hidden = True
            End If
        Next
    End If
End Sub
 

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
17
Try:

Code:
Private Sub CheckBox1_Click()
    Dim uc As Long, i As Long
    Application.ScreenUpdating = False
    uc = Cells(2, Columns.Count).End(xlToLeft).Column
    Cells.EntireColumn.Hidden = False
    If CheckBox1 = True Then
        For i = Columns("D").Column To uc
            If Not (Cells(2, i).Value >= Range("B6").Value And Cells(2, i).Value <= Range("B7").Value) Then
                Columns(i).Hidden = True
            End If
        Next
    End If
End Sub


my friend, it works like a charm :)

thank you so much for your help, but now that i have test this code pop me a new question.

there is a way to have this "automatically"? With the checkbox (my first guess) you need to check every time the conditions changes.

a worksheet change event isn´t a possibility? and, imagine that the workbook has 20 pages, all with the same position of the columns, can i do this (on a support sheet) that applies this rule to a group of sheets?

Once again, thank you very much for your help.

Kind regards mate,
André
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Put the following macro in the Thisworkbook events, each time you change the sheet, it will run automatically.
If the events do not run automatically, first run this macro:

Code:
Sub en()
Application.EnableEvents = True
End Sub


Note: Your first sheet must have the name "support"

Code:
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim uc As Long, i As Long
    Dim sh1 As Worksheet
    '
    Set sh1 = Sheets("support")
    Application.ScreenUpdating = False
    For i = 13 To sh1.Range("A" & Rows.Count).End(xlUp).Row
        If sh.Name = sh1.Cells(i, "A").Value And sh1.Cells(i, "B").Value = "YES" Then
            sh.Cells.EntireColumn.Hidden = False
            uc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
            For j = Columns("D").Column To uc
                If Not (sh.Cells(2, j).Value >= sh1.Range("B6").Value And _
                        sh.Cells(2, j).Value <= sh1.Range("B7").Value) Then
                    sh.Columns(j).Hidden = True
                End If
            Next
            Exit For
        End If
    Next
End Sub

https://www.dropbox.com/s/2jcz8eh86legz1k/Book2 dam.xlsm?dl=0
 

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
17
Put the following macro in the Thisworkbook events, each time you change the sheet, it will run automatically.
If the events do not run automatically, first run this macro:

Code:
Sub en()
Application.EnableEvents = True
End Sub


Note: Your first sheet must have the name "support"

Code:
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim uc As Long, i As Long
    Dim sh1 As Worksheet
    '
    Set sh1 = Sheets("support")
    Application.ScreenUpdating = False
    For i = 13 To sh1.Range("A" & Rows.Count).End(xlUp).Row
        If sh.Name = sh1.Cells(i, "A").Value And sh1.Cells(i, "B").Value = "YES" Then
            sh.Cells.EntireColumn.Hidden = False
            uc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
            For j = Columns("D").Column To uc
                If Not (sh.Cells(2, j).Value >= sh1.Range("B6").Value And _
                        sh.Cells(2, j).Value <= sh1.Range("B7").Value) Then
                    sh.Columns(j).Hidden = True
                End If
            Next
            Exit For
        End If
    Next
End Sub

https://www.dropbox.com/s/2jcz8eh86legz1k/Book2 dam.xlsm?dl=0


DanteAmor

My friend, you re a f*** genius :)

works perfect as the other one does it. all i need to do is add more "name sheets" and keep on rolling :)

Many thanks my friend.

Kind regards

an once again, tks ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,806
Messages
5,638,475
Members
417,026
Latest member
UDK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top