Hide the column if the header contains specific text

[CaptainCsaba]

Hi everyone! I am trying to finish up a macro by hiding columns we don't need in our work. After the macro runs, many columns are left that we don't use 99.9% of the time, so hiding it would be great. Problem is that they are not always in the same column and based on the base file the header could contain different words, number of spaces etc. So what i would need is a way to hide a column if it contains a specific word. So if the column is named "Asset amount" then if gets hidden if the macro sees the word "asset" for example. It's is important that the rule should only work for the header because some words could appear elsewhere. Thank you!

 

[Fluff]

How about

Sub HideCols()

   Dim Qty As Long
   Dim Cnt As Long
   Dim Fnd As Range
   
   Set Fnd = Range("A1")
   Qty = WorksheetFunction.CountIf(Rows(1), "*asset*")
   For Cnt = 1 To Qty
      Set Fnd = Range("1:1").Find("asset", Fnd, , xlPart, , , False, , False)
      Fnd.EntireColumn.Hidden = True
   Next Cnt
   
End Sub

This assumes that your header is in row 1

 

[wideboydixon]

On the off-chance that there are other words you want to hide, you could use something like this:

Public Sub HideColumn(columnText As String, headerRow As Long)

Dim lastCol As Long
Dim thisCol As Long

lastCol = Cells(headerRow, Columns.Count).End(xlToLeft).Column
For thisCol = 1 To lastCol
    If InStr(1, Cells(headerRow, thisCol).Text, columnText, vbTextCompare) > 0 Then
        Cells(headerRow, thisCol).EntireColumn.Hidden = True
    End If
Next thisCol

End Sub

And call it like this:

HideColumn "asset", 1

WBD