Forum Discussion

JShearer98's avatar
JShearer98
Copper Contributor
Sep 09, 2025

Macro to filter table to a searched result, then hide columns that contain a blank or a set value.

Hi All,

 

I'm trying to figure out how to do the above. Essentially I have a list of assemblies and along the same row is a list of each part within that assembly. Not every sub-part is used in each assembly so there are blank spaces. I currently have a Search cell in B2 with a macro button next to it to Autofilter the table in B5 to the assembly I want to see. I'm struggling to get a reliable way to select the line that is visible after filtering so I can hide columns containing no information. Also if there are any better ways to hide the columns then seen below as I just recorded a macro of hiding them. The table I have spans from B5 to W808 currently. Range("C7:W7").Select works but the result will not always be in that row as only hiding cells above it.

Sub Search()

Application.ScreenUpdating = False

If Range("B2") <> "" Then

          ActiveSheet.Range("B5").AutoFilter Field:=1, Criteria1:=Cells(2, 2).Value    
        
          Rows(Cells("A2").Value).Select 'Doesn't Work

          Selection.SpecialCells(xlCellTypeBlanks).Select
          Selection.EntireColumn.Hidden = True

End If

Application.ScreenUpdating = True

 

excel
Macros and VBA

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 09, 2025

    Does this do what you want?

    Sub SEARCH()
    Dim rng As Range
    Application.ScreenUpdating = False
    Range("B2:W2").EntireColumn.Hidden = False
    If Range("B2") <> "" Then
        Range("B5").AutoFilter Field:=1, Criteria1:=Range("B2").Value
        On Error Resume Next
        Set rng = Range("B" & Rows.Count).End(xlUp).Resize(1, 22).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rng Is Nothing Then
            rng.EntireColumn.Hidden = True
        End If
    End If
    Application.ScreenUpdating = True
End Sub

     

Resources