VBA - Hiding Columns based on cell values

[smitty120]

Hello,

I’ve really appreciated the support this board has given me as I learn VBA. I have three columns – Q, R, and S. I only need to see columns R and S if the cell values don’t equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don’t need to see columns R and S.
[TABLE="width: 287, align: center"]
<tbody>[TR]
[TD]Q Header[/TD]
[TD]R Header[/TD]
[TD]S Header[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


I’ve been stumped on this for a while, and I hope I’ve explained it clearly. Thanks for your help!

 

[JoeMo]

This code for the sheet of interest. Whenever you change a value in Column Q the code will be triggered and will hide columns R & S if the values in each cell from Q through S in every populated row are the same.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("Q")) Is Nothing Then
    Dim lR As Long, vA As Variant, i As Long
    lR = Range("Q" & Rows.Count).End(xlUp).Row
    vA = Range("Q2", "S" & lR).Value
    Application.ScreenUpdating = False
    For i = LBound(vA, 1) To UBound(vA, 1)
        If vA(i, 1) <> vA(i, 2) Or vA(i, 1) <> vA(i, 3) Then
            Columns("R:S").Hidden = False
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next i
    Columns("R:S").Hidden = True
    Application.ScreenUpdating = True
End If
End Sub

 

[smitty120]

JoeMo,

I apologize it's taken me awhile to get back to you. I took a long weekend and have spent this week playing catch-up. Thank you for taking the time to respond to me. These cell values are in a spreadsheet I receive on a daily basis, and I wouldn't be making any changes to the cells but rather hiding or unhiding the columns based on the values already present. How would I alter the code for static values instead of the dynamic values you're thinking about? I'll play with it and see if I can alter it myself, but I would love your help. Thanks again!

 

[JoeMo]

JoeMo,

I apologize it's taken me awhile to get back to you. I took a long weekend and have spent this week playing catch-up. Thank you for taking the time to respond to me. These cell values are in a spreadsheet I receive on a daily basis, and I wouldn't be making any changes to the cells but rather hiding or unhiding the columns based on the values already present. How would I alter the code for static values instead of the dynamic values you're thinking about? I'll play with it and see if I can alter it myself, but I would love your help. Thanks again!

Easiest procedure would be to install the code using the instructions I posted, then when you are ready to run it, simply click on a cell in column Q and re-enter the value that's already there. That will trigger the code.

Alternatively, you can install this as a standard module (not a sheet module) and run it on the active sheet after you receive it.

Sub HideOrUnhideColumns()
Dim lR As Long, vA As Variant, i As Long
    lR = Range("Q" & Rows.Count).End(xlUp).Row
    vA = Range("Q2", "S" & lR).Value
    Application.ScreenUpdating = False
    For i = LBound(vA, 1) To UBound(vA, 1)
        If vA(i, 1) <> vA(i, 2) Or vA(i, 1) <> vA(i, 3) Then
            Columns("R:S").Hidden = False
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next i
    Columns("R:S").Hidden = True
    Application.ScreenUpdating = True
End Sub

 

[smitty120]

JoeMo,

Thanks for getting back to me. I ran the standard module in my sandbox and it worked like a charm. I've been stuck on this one for weeks, so I really appreciate your help!