Hide\UnHide columns - automatically base on cell value

[Andre Pereira]

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,

 

[DanteAmor]

Put a checkbox on your sheet and add the following 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]

Put a checkbox on your sheet and add the following 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]

Put a checkbox on your sheet and add the following 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]

Try:

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]

Try:

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é

 

[Andre Pereira]

My friend,

i just add another file with the example that i need.

https://www.dropbox.com/s/d8ihjztj09ze0cx/Book2.xlsx?dl=0

If what i'm asking isn´t possible, the code that you sent (2nd time) works.

Regards
André

 

[DanteAmor]

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:

Sub en()
Application.EnableEvents = True
End Sub

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

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]

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:

Sub en()
Application.EnableEvents = True
End Sub

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

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

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.