How to automatically hide/unhide columns based on criteria with VBA

[WentzelN94]

Hi
I used the following code to automatically hide/unhide rows based on conditions but do not have the skills to also apply this to columns.

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim m As Long
    If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Me.Range("AZ:AZ").EntireRow.Hidden = False
        m = Range("AZ" & Me.Rows.Count).End(xlUp).Row
        For r = 12 To m
            If Range("AZ" & r).Value = False Then
                Range("AZ" & r).EntireRow.Hidden = True
            End If
        Next r
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

So what I need is that I am using column C to T for my data. In row 210 I have a formula that if D5 is empty it should return a False. If all shells from D5 to T5 is empty then only column D should show. When D5 is populated with a value then column E should unhide and so on until D5 to T5 is populated and all columns is unhided.

Thank you

 

[GSP75]

Hi
I used the following code to automatically hide/unhide rows based on conditions but do not have the skills to also apply this to columns.

Public Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim m As Long
If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Me.Range("AZ:AZ").EntireRow.Hidden = False
m = Range("AZ" & Me.Rows.Count).End(xlUp).Row
For r = 12 To m
If Range("AZ" & r).Value = False Then
Range("AZ" & r).EntireRow.Hidden = True
End If
Next r
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

So what I need is that I am using column C to T for my data. In row 210 I have a formula that if D5 is empty it should return a False. If all shells from D5 to T5 is empty then only column D should show. When D5 is populated with a value then column E should unhide and so on until D5 to T5 is populated and all columns is unhided.

Thank you

Hi,

To extend the code to hide/unhide columns based on your conditions, you can modify the existing code as follows:

Public Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim m As Long If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Me.Range("AZ:AZ").EntireRow.Hidden = False Me.Range("C:T").EntireColumn.Hidden = True 'Hide all columns initially If Range("D5").Value = "" Then 'Check if D5 is empty Range("D:D").EntireColumn.Hidden = False 'Unhide only column D Else For i = 1 To 17 'Loop through columns C to T If Range("D5:T5").Cells(1, i).Value <> "" Then 'Check if the cell is not empty Range(Cells(1, i + 2), Cells(200, i + 2)).EntireColumn.Hidden = False 'Unhide the corresponding column End If Next i End If Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub

This code will check if cell D5 is empty and if it is, only column D will be unhidden. If D5 is not empty, it will loop through columns C to T and unhide the corresponding column if the cell is not empty. Note that I have assumed that you have data in rows 1 to 200.

Hope this helps!

 

[GSP75]

Hi
I used the following code to automatically hide/unhide rows based on conditions but do not have the skills to also apply this to columns.

Public Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim m As Long
If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Me.Range("AZ:AZ").EntireRow.Hidden = False
m = Range("AZ" & Me.Rows.Count).End(xlUp).Row
For r = 12 To m
If Range("AZ" & r).Value = False Then
Range("AZ" & r).EntireRow.Hidden = True
End If
Next r
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

So what I need is that I am using column C to T for my data. In row 210 I have a formula that if D5 is empty it should return a False. If all shells from D5 to T5 is empty then only column D should show. When D5 is populated with a value then column E should unhide and so on until D5 to T5 is populated and all columns is unhided.

Thank you

Hi I used the following code to automatically hide/unhide rows based on conditions but do not have the skills to also apply this to columns. Public Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim m As Long If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Me.Range("AZ:AZ").EntireRow.Hidden = False m = Range("AZ" & Me.Rows.Count).End(xlUp).Row For r = 12 To m If Range("AZ" & r).Value = False Then Range("AZ" & r).EntireRow.Hidden = True End If Next r Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub So what I need is that I am using column C to T for my data. In row 210 I have a formula that if D5 is empty it should return a False. If all shells from D5 to T5 is empty then only column D should show. When D5 is populated with a value then column E should unhide and so on until D5 to T5 is populated and all columns is unhided. Thank you



Hello,
To achieve your goal of automatically hiding/unhiding columns based on the values in row 210, you can modify your existing VBA code to include the following:

1. Replace the existing range "B7:B200" in the first line of the code with "C210:T210". This will trigger the code to run whenever a change is made to any cell in row 210.
2. Add a new loop to hide/unhide columns based on the values in row 210. Here's an example code that you can use:

vbnetCopy code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Long
Dim m As Long, n As Long
   
'Check if the changed cell is in row 210
If Not Intersect(Me.Range("C210:T210"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
       
'Hide all columns initially
Me.Range("C:T").EntireColumn.Hidden = True
       
'Find the last populated column in row 210
n = Me.Cells(210, Me.Columns.Count).End(xlToLeft).Column
       
'Unhide columns D to the last populated column
For c = 4 To n
If Me.Cells(210, c).Value <> "" Then
Me.Columns(c).EntireColumn.Hidden = False
End If
 Next c
       
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

This code checks if a change has been made to any cell in row 210, and then hides all columns initially. It then finds the last populated column in row 210, and unhides columns D to the last populated column based on their values.
Please note that this code assumes that the values in row 210 are either blank or non-blank. If you have other conditions that determine when to hide/unhide columns, you may need to modify the code accordingly.
I hope this helps!

 

[Peter_SSs]

@WentzelN94 @GSP75
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[WentzelN94]

Hi I used the following code to automatically hide/unhide rows based on conditions but do not have the skills to also apply this to columns. Public Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim m As Long If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Me.Range("AZ:AZ").EntireRow.Hidden = False m = Range("AZ" & Me.Rows.Count).End(xlUp).Row For r = 12 To m If Range("AZ" & r).Value = False Then Range("AZ" & r).EntireRow.Hidden = True End If Next r Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub So what I need is that I am using column C to T for my data. In row 210 I have a formula that if D5 is empty it should return a False. If all shells from D5 to T5 is empty then only column D should show. When D5 is populated with a value then column E should unhide and so on until D5 to T5 is populated and all columns is unhided. Thank you



Hello,
To achieve your goal of automatically hiding/unhiding columns based on the values in row 210, you can modify your existing VBA code to include the following:

1. Replace the existing range "B7:B200" in the first line of the code with "C210:T210". This will trigger the code to run whenever a change is made to any cell in row 210.
2. Add a new loop to hide/unhide columns based on the values in row 210. Here's an example code that you can use:

vbnetCopy code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Long
Dim m As Long, n As Long
  
'Check if the changed cell is in row 210
If Not Intersect(Me.Range("C210:T210"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
      
'Hide all columns initially
Me.Range("C:T").EntireColumn.Hidden = True
      
'Find the last populated column in row 210
n = Me.Cells(210, Me.Columns.Count).End(xlToLeft).Column
      
'Unhide columns D to the last populated column
For c = 4 To n
If Me.Cells(210, c).Value <> "" Then
Me.Columns(c).EntireColumn.Hidden = False
End If
 Next c
      
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

This code checks if a change has been made to any cell in row 210, and then hides all columns initially. It then finds the last populated column in row 210, and unhides columns D to the last populated column based on their values.
Please note that this code assumes that the values in row 210 are either blank or non-blank. If you have other conditions that determine when to hide/unhide columns, you may need to modify the code accordingly.
I hope this helps!

Hi

I tried the code but it does not seem to work as it does not hide any columns.

I have a formula in row 210 so the worksheet_Change will not work as I VBA does not recognise a formula change as a change event.

But I have tried to change the code to detect any changes in row 5 to initiate the macro then but no luck.

 

[WentzelN94]

Hi

I found a solution for my problem but it seems to lag a bit with the coding on the columns.
Is there maybe a solution for the coding to run more efficient?

Thank you

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim m As Long
    Dim c As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        If Not Intersect(Me.Range("B7:B200"), Target) Is Nothing Then
            Me.Range("AZ:AZ").EntireRow.Hidden = False
            m = Range("AZ" & Me.Rows.Count).End(xlUp).Row
            For r = 12 To m
                If Range("AZ" & r).Value = False Then
                    Range("AZ" & r).EntireRow.Hidden = True
                End If
            Next r
        End If
        If Not Intersect(Me.Range("C5:T5"), Target) Is Nothing Then
            For Each c In Rows("210:210").Cells
                c.EntireColumn.Hidden = (c.Value = "False")
            Next c
        End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub