How to show / hide selected columns based on cell values

[Nuke_It_Newport]

Hi everyone-

I have a worksheet ("IMPORT") that I want to show / hide columns based on selections from another worksheet ("~SETTINGS").

The worksheet ("~SETTINGS") has a table with the column names from the "IMPORT" sheet in the first column, and a "TRUE" or "FALSE" value in the second column. The end user will select whether to show or hide the columns on this sheet.

I have the following code that puts these values into an array.

Option Explicit

Sub arrTest()

Dim lRow As Long
Dim myAddress As String
Dim dataArray As Variant

Dim rowStart As Long, rowEnd As Long
Dim colStart As Long, colEnd As Long

Dim rowCtr As Long
Dim colCtr As Long
'   Need to select or activate settings sheet!

With Sheets("~SETTINGS")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
myAddress = "$A$3:$B" & lRow
dataArray = Range(myAddress).Value2

rowStart = LBound(dataArray, 1)
rowEnd = UBound(dataArray, 1)
colStart = LBound(dataArray, 2)
colEnd = UBound(dataArray, 2)

For rowCtr = rowStart To rowEnd
    For colCtr = colStart To colEnd
        Debug.Print rowCtr & ":" & colCtr, vbTab & dataArray(rowCtr, colCtr)
    Next
    '   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
    '   $$    If rowCtr, column 2 = "TRUE" Then hide worksheet name from rowCtr, column 1    $$
    '   $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Next
End With

End Sub

This code outputs the array in the Debug.Print window as follows:

What I need to do is loop through the array, look at each "TRUE" or "FALSE" entry, and then show or hide the column name in the position above. For example, if position 19:2 is "False", then get the column name from position 19:1, which is "Completed Date", and hide this column on the "IMPORT" sheet.

I got this code from somewhere, and am trying to modify it to meet my needs. Is there a simpler way?

Thanks!

Chad

 

[Wookiee]

Does your solution require that you read those values into an array to evaluate which columns to hide? Because I created a macro based on your examples and it worked by just looping through the values and comparing the header text. Hope this helps!

Sub NukePort()
'Crafted by Wookiee at MrExcel.com


Dim strHeader  As String
Dim rngCell    As Range
Dim rngCells   As Range
Dim rngHeaders As Range
Dim wksImp     As Worksheet
Dim wksSets    As Worksheet

Set wksImp = ThisWorkbook.Sheets("IMPORT")
Set wksSets = ThisWorkbook.Sheets("SETTINGS")

With wksImp

  Set rngHeaders = .Range("A3:S3")

End With

With wksSets

  For Each rngCell In .Range("A3:A21")

    If rngCell.Offset(, 1) = True Then
      
      strHeader = rngCell.Text

      For Each rngCells In rngHeaders

        If rngCells.Text = strHeader Then

            rngCells.EntireColumn.Hidden = True

        End If

      Next rngCells

    End If

  Next rngCell

  .Activate

End With

Set wksImp = Nothing
Set wksSets = Nothing


End Sub

 

[Nuke_It_Newport]

Does your solution require that you read those values into an array to evaluate which columns to hide? Because I created a macro based on your examples and it worked by just looping through the values and comparing the header text. Hope this helps!

Sub NukePort()
'Crafted by Wookiee at MrExcel.com


Dim strHeader  As String
Dim rngCell    As Range
Dim rngCells   As Range
Dim rngHeaders As Range
Dim wksImp     As Worksheet
Dim wksSets    As Worksheet

Set wksImp = ThisWorkbook.Sheets("IMPORT")
Set wksSets = ThisWorkbook.Sheets("SETTINGS")

With wksImp

  Set rngHeaders = .Range("A3:S3")

End With

With wksSets

  For Each rngCell In .Range("A3:A21")

    If rngCell.Offset(, 1) = True Then
     
      strHeader = rngCell.Text

      For Each rngCells In rngHeaders

        If rngCells.Text = strHeader Then

            rngCells.EntireColumn.Hidden = True

        End If

      Next rngCells

    End If

  Next rngCell

  .Activate

End With

Set wksImp = Nothing
Set wksSets = Nothing


End Sub

That's exactly what I needed. Thank you!!