Hide columns within a Range based on Row value

Question

I'm trying to hide columns in a sheet. I have the following script. I want the range to be dynamic. Right now it's looking at the sheet, the table and then column 3 row 3 to the end of the table.

I want the script to look at column 3 in the table, then within column 3, find the row that contains a value equal to the value of Cell A4. Then use that row to the end of the table to do the If Not search.

I can't figure out a way to get the range to set a row value based on a cell value.

Sub Role_Filter_Button()

    Dim cl As Range, rTest As Range

    'Sets the range by starting with the third column in a table
    Set rTest = Range(Worksheets("Know Our Business").ListObjects("Know_Our_Business").DataBodyRange(3, 3), Worksheets("Know Our Business").ListObjects("Know_Our_Business").DataBodyRange(3, 3).End(xlToRight))
    For Each cl In rTest
        If Not InStr(1, cl.Value, Worksheets("Know Our Business").Range("A4").Value) > 0 Then
        cl.EntireColumn.Hidden = True
        End If
     Next cl
End Sub

SilentRevolution SilentRevolution · Accepted Answer · 2016-01-18T22:51:44

I've added a second loop around which first looks through column 3. A counter counts the rows and if a value in column 3 matches the value in a4, i is then used to set the row which is used for your code.

Sub Role_Filter_Button()

    Dim cla As Range, clb As Range, rTest As Range
    Dim i As Long

    Dim dTable As ListObject

    Set dTable = ThisWorkbook.Worksheets("Know Our Business").ListObjects("Know_Our_Business")
    i = 1

    For Each cla In dTable.ListColumns(3).Range
        If cla = ThisWorkbook.Worksheets("Know Our Business").Cells(4, 1) Then
            Set rTest = Range(dTable.DataBodyRange(i, 3), dTable.DataBodyRange(i, 3).End(xlToRight))
                For Each clb In rTest
                    If Not InStr(1, cla.Value, Worksheets("Know Our Business").Range("A4").Value) > 0 Then
                        clb.EntireColumn.Hidden = True
                    End If
                Next clb
        End If
        i = i + 1
    Next cla

End Sub

Hide columns within a Range based on Row value

1 Answers