Hide columns based on IF

[288enzo]

Hi all,

I'm having trouble figuring out where to start with this and could use your guidance.

First off, the total rows are ever changing and so I would need to use "last row" in my formula. The columns in question will always begin at O and end at AS.

My goal is to hide all columns that do not have an "N" in any particular cell within that specific column.

I had thought of a loop within a loop, beginning looping through the columns and then looping through the rows, but I'm not sure how to pull this off.

Thank you

 

[kevin9999]

Please try the following on a copy of your workbook. Place the code in the sheet module of the sheet in question - right click the sheet tab name, select View Code, and put the code in the window that appears on the right of the screen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("O:AS"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim i As Long, r As Range
        For i = 15 To 45
            Set r = Columns(i).Find("N")
            If r Is Nothing Then Columns(i).Hidden = True
        Next i
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[288enzo]

Please try the following on a copy of your workbook. Place the code in the sheet module of the sheet in question - right click the sheet tab name, select View Code, and put the code in the window that appears on the right of the screen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("O:AS"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim i As Long, r As Range
        For i = 15 To 45
            Set r = Columns(i).Find("N")
            If r Is Nothing Then Columns(i).Hidden = True
        Next i
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I did as you said, right click on sheet tab name, pasted the code. When I try to run it I get a pop up looking for a macro to run.

One important detail I failed to include, the file I'm running the macro on is generated by a database, I would need the macro to be on another file where I would run it. As an example, I have a file simply called Misc.xlsb that houses all my macros. I open that when I need to run one of my macros.

On a side note, I'm unfamiliar with the following line, how does it work?

If Not Intersect(Range("O:AS"), Target) Is Nothing Then

Thank you

 

[288enzo]

I came up with this rather rudimentary macro.

Sub hide_zero()
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Range("O" & LR + 1) = "=COUNTIF(O4:O" & LR & ",""N"")"
    Range("O" & LR + 1).Copy Range("P" & LR + 1 & ":AS" & LR + 1)
    For x = 15 To 45
        If Range("A" & LR + 1).Offset(0, x) = 0 Then Range("A" & LR + 1).Offset(0, x).EntireColumn.Hidden = True
    Next x
End Sub

 

[kevin9999]

When I try to run it

Worksheet change event code does not require any actions to run it - it runs automatically when a change occurs, in this case in columns O:AS. Being a worksheet level code, it won't appear on the macro list.

One important detail I failed to include

Yes, that does make a considerable difference. If your "rudimentary macro" in post #4 works for you, then I could suggest the following, which is run with the relevant sheet active:

Option Explicit
Sub Hide_Some_Columns()
    Application.ScreenUpdating = False
    Dim i As Long, r As Range
    For i = 15 To 45
        Set r = Columns(i).Find("N")
        If r Is Nothing Then Columns(i).Hidden = True
    Next i
    Application.ScreenUpdating = False
End Sub

I'm unfamiliar with the following line, how does it work?

In simple terms, it says only run the macro if a change occurs in the range O:AS - in other words, if the cell (Target) where the change occurs intersects with range O:AS.

 

[288enzo]

Yes, that does make a considerable difference. If your "rudimentary macro" in post #4 works for you, then I could suggest the following, which is run with the relevant sheet active:

Option Explicit
Sub Hide_Some_Columns()
    Application.ScreenUpdating = False
    Dim i As Long, r As Range
    For i = 15 To 45
        Set r = Columns(i).Find("N")
        If r Is Nothing Then Columns(i).Hidden = True
    Next i
    Application.ScreenUpdating = False
End Sub

I understand this, but I believe the Find is looking for an "N" anywhere in the cell, I need it to only look for cells with "N" and nothing else. As an example, "N/A" in a cell is recognized as an "N", is that right?

Thank you.

 

[kevin9999]

Understood. Try this instead:

Option Explicit
Sub Hide_Some_Columns()
    Application.ScreenUpdating = False
    Dim i As Long, r As Range
    For i = 15 To 45
        Set r = Columns(i).Find("N", LookAt:=xlWhole)
        If r Is Nothing Then Columns(i).Hidden = True
    Next i
    Application.ScreenUpdating = False
End Sub