Hide rows in multiple columns without any data

[rjplante]

I have a table with 14 column in it. The columns are grouped into pairs 3 and 4, 5 and 6, etc. I have row 4 set aside with a check cell in the second column for each pair, that will either have a YES or a NO in it. A user can compare data in what ever column has a YES in the second column. I would like to have a macro that will hide all the rows without data in the first column of the set (3, 5, 7, 9, 11, 13, and 15) for each column with a YES in the second column. If multiple columns are selected, rows for the first set of columns (3-4) may not have any data in them, but there is data in the second set of columns (5-6), so the row should be displayed and vice versa. If a single set of columns is selected (3-4) then all rows without data in column 3 will be hidden. And this would apply for any single set of columns selected. If there is no data in the first column for a particular row, then hide that row.

My code below is to hide all of the columns that have a NO in the second set of columns. I just need to figure out how to apply the filter on the rows and across multiple columns. I am currently at a loss as to where to begin.

Dim n As Integer

Sheets("Data Table").Range("C4").Select

n = 3

For n = 3 To 16

    If Cells(4, n).Value = "NO" Then
   
        Columns(n).EntireColumn.Hidden = True
        Columns(n - 1).EntireColumn.Hidden = True
       
    End If

Next n

 

[Zot]

You said, If multiple columns are selected. How are they selected before macro is run? The macro need to know which columns are selected.

 

[Peter_SSs]

Give this a try with a copy of your workbook.
I have assumed that column Z could be used as a helper column.

Sub Hide_Rows()
  Dim a As Variant, b As Variant
  Dim lr As Long, r As Long, c As Long, k As Long
 
  lr = Columns("C:P").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  With Range("C4:P" & lr)
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    b(1, 1) = 1
    For c = 2 To UBound(a, 2) Step 2
      If a(1, c) = "YES" Then
        For r = 2 To UBound(a)
          If Len(a(r, c - 1)) > 0 Then
            b(r, 1) = 1
            k = 1
          End If
        Next r
      End If
    Next c
    If k = 1 Then
      With Range("Z4").Resize(UBound(b))
        .Value = b
        .SpecialCells(xlBlanks).EntireRow.Hidden = True
        .ClearContents
      End With
    End If
  End With
End Sub

 

[rjplante]

Thank you Peter, I just had to add a couple of lines to unprotect and protect my sheet after the macro was done and the rest worked great. I appreciate the help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.