CODEWITHSUNDEEP
vbaexcel
R Mannix
R Mannix

Reputation: 15

Adapting VBA Code to Hide Columns based off values from multiple rows

I have a piece of code I've used to hide columns based off of values being in that column, essentially it looks at all cells in that column underneath a certain row and if there's a value in there it'll keep it showing and if not it'll hide it.

Now I need it to also hide things based off values from a specific row. This is the code:

Sub HideCols()
Dim LC As Integer, j As Integer
Dim LR As Integer, curCnt As Integer
Dim k As Integer
Dim Data As Variant

Application.ScreenUpdating = False

LC = Cells(3, Columns.Count).End(xlToLeft).Column

For j = 6 To LC
    LR = Cells(Rows.Count, j).End(xlUp).Row
    curCnt = 0
    Data = Range(Cells(1, 1), Cells(LR, LC))
    For k = 3 To LR
       If Rows(k).Hidden = False And Data(k, j) <> "" Then _
       curCnt = curCnt + 1
    Next k
    Columns(j).Hidden = curCnt < 2
Next j

Application.ScreenUpdating = True

End Sub

I tried adding:

Dim i As Long
Dim c As Variant
Dim l As Integer
For i = 6 To j
    For Each c In ActiveSheet.Cells(2, i)
    If Columns(i).Hidden and c.Value Like "Tri-Annual" Then
        ActiveSheet.Columns(i).Hidden = False
    Else
        ActiveSheet.Columns(i).Hidden = True
    End If
    Next c
Next i

This was added in following, so the hope was that it would only look at the columns that weren't hidden by the first macro and then hide all columns that don't also have "Tri-Annual" in that column in row 2. It does complete the task, but I have to run it twice. Is there any easier way of doing this?

Upvotes: 0

Views: 99

Answers (1)

SJR
SJR

Reputation: 23081

Try this. I think I have it the right way round.

Sub HideCols()

Dim LC As Long, j As Long
Dim LR As Long, curCnt As Long
Dim k As Long
Dim Data As Variant

Application.ScreenUpdating = False

LC = Cells(3, Columns.Count).End(xlToLeft).Column

For j = 6 To LC
    LR = Cells(Rows.Count, j).End(xlUp).Row
    curCnt = 0
    Data = Range(Cells(1, 1), Cells(LR, LC))
    For k = 3 To LR
       If Rows(k).Hidden = False And Data(k, j) <> "" Then _
       curCnt = curCnt + 1
    Next k
    Columns(j).Hidden = curCnt < 2 Or Cells(2, j).Value <> "Tri-Annual"
Next j

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions