Hide\UnHide columns - automatically base on cell value

A

Andre Pereira

New Member
Joined
Sep 3, 2018
Messages
21
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,
 
Sort by date Sort by votes
DanteAmor said:
I'm glad to help you. Thanks for the feedback.
Click to expand...

Dante,

The code works fine and i want to apply this code to another templates, but doesn't work

i change the values in red, because the other sheets data begin in L13 (the example begins at E2)

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(13, Columns.Count).End(xlToLeft).Column
For j = Columns("k").Column To uc
If Not (sh.Cells(13, j).Value >= sh1.Range("B6").Value And _
sh.Cells(13, j).Value <= sh1.Range("B7").Value) Then
sh.Columns(j).Hidden = True
End If
Next
Exit For
End If
Next
End Sub

Once again thank you, and forgive me for being so noob :(
André
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
DanteAmor said:
All the sheets start at L13 or only some?
Click to expand...

In this specific case, all of them.

is there a hypotheses to, in support sheet to name the range and apply this range to IF statement?

i said this, because, if i'm not mistaken, the

uc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
For j = Columns("D").Column To uc

---> Starts in column "D" and row 2 and goes to the end, right?

Example

[TABLE="width: 397"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Sheets[/TD]
[TD]Aply MACRO?[/TD]
[TD]Row Start[/TD]
[TD]Column Start[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD]YES[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD]YES[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD]YES[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD]no[/TD]
[TD="align: right"]4[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Sheet5[/TD]
[TD]no[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet6[/TD]
[TD]YES[/TD]
[TD="align: right"]12[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Andre Pereira said:
In this specific case, all of them.

is there a hypotheses to, in support sheet to name the range and apply this range to IF statement?

i said this, because, if i'm not mistaken, the

uc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
For j = Columns("D").Column To uc

---> Starts in column "D" and row 2 and goes to the end, right?

Example

[TABLE="width: 397"]
<tbody>[TR]
[TD]Sheets[/TD]
[TD]Aply MACRO?[/TD]
[TD]Row Start[/TD]
[TD]Column Start[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD]YES[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD]YES[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD]YES[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD]no[/TD]
[TD="align: right"]4[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Sheet5[/TD]
[TD]no[/TD]
[TD="align: right"]13[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Sheet6[/TD]
[TD]YES[/TD]
[TD="align: right"]12[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Try this

Code: 
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim uc As Long, i As Long, wRow As Long, wCol As String
    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
            wRow = sh1.Cells(i, "C").Value
            wCol = sh1.Cells(i, "D").Value
            sh.Cells.EntireColumn.Hidden = False
            uc = sh.Cells(wRow, Columns.Count).End(xlToLeft).Column
            For j = Columns(wCol).Column To uc
                If Not (sh.Cells(wRow, j).Value >= sh1.Range("B6").Value And _
                        sh.Cells(wRow, j).Value <= sh1.Range("B7").Value) Then
                    sh.Columns(j).Hidden = True
                End If
            Next
            Exit For
        End If
    Next
End Sub
 
Upvote 0
DanteAmor said:
Try this

Code: 
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim uc As Long, i As Long, wRow As Long, wCol As String
    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
            wRow = sh1.Cells(i, "C").Value
            wCol = sh1.Cells(i, "D").Value
            sh.Cells.EntireColumn.Hidden = False
            uc = sh.Cells(wRow, Columns.Count).End(xlToLeft).Column
            For j = Columns(wCol).Column To uc
                If Not (sh.Cells(wRow, j).Value >= sh1.Range("B6").Value And _
                        sh.Cells(wRow, j).Value <= sh1.Range("B7").Value) Then
                    sh.Columns(j).Hidden = True
                End If
            Next
            Exit For
        End If
    Next
End Sub
Click to expand...

i think that i am doing something wrong, because, no matter value i add on column C or D, or changing the row number in a list the results are the same.

i join another file so you can test it please.

https://www.dropbox.com/s/nmxi6lf1xvxis5q/Example2.xlsm?dl=0

Thank you for all your effort,
André
 
Upvote 0
Hi Dante,

hope you're well

i been using the file that you attached and its working fine. Could you please send the code?

Kind regard my friend

André
 
Upvote 0
Code: 
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim uc As Long, i As Long, wRow As Long, wCol As String
    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
            wRow = sh1.Cells(i, "C").Value
            wCol = sh1.Cells(i, "D").Value
            sh.Cells.EntireColumn.Hidden = False
            uc = sh.Cells(wRow, Columns.Count).End(xlToLeft).Column
            For j = Columns(wCol).Column To uc
                If Not (sh.Cells(wRow, j).Value >= sh1.Range("B6").Value And _
                        sh.Cells(wRow, j).Value <= sh1.Range("B7").Value) Then
                    sh.Columns(j).Hidden = True
                End If
            Next
            Exit For
        End If
    Next
End Sub
 
Upvote 0
DanteAmor said:
Code: 
Private Sub Workbook_SheetActivate(ByVal sh As Object)
    Dim uc As Long, i As Long, wRow As Long, wCol As String
    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
            wRow = sh1.Cells(i, "C").Value
            wCol = sh1.Cells(i, "D").Value
            sh.Cells.EntireColumn.Hidden = False
            uc = sh.Cells(wRow, Columns.Count).End(xlToLeft).Column
            For j = Columns(wCol).Column To uc
                If Not (sh.Cells(wRow, j).Value >= sh1.Range("B6").Value And _
                        sh.Cells(wRow, j).Value <= sh1.Range("B7").Value) Then
                    sh.Columns(j).Hidden = True
                End If
            Next
            Exit For
        End If
    Next
End Sub
Click to expand...

Man, i feel like dumb as hell :(

i'm truly sorry for wasting your time when i have been so fricking dumb :(

i was changing the sheet code instead macro code, that's why i don't get the results, now i am applying the changes directly in macro code and it's working like a charm.

Once again, i m so grateful to you my friend

kind regards
André
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA to Automatically Hide/Unhide Dynamic Range based Upon Cell Value
Replies
3
Views
195
franswa3434
F
N
  • Question Question
How to Hide and Unhide Sheets Based on Cell Value
2
Replies
11
Views
679
nhinx
N
Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
344
ysfuyar
Y
N
  • Question Question
Toggle code to hide and unhide columns based on text in a row
Replies
1
Views
489
Cubist
Cubist
PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
1K
PuntingJawa
PuntingJawa

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

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
Back
Top