Hide Columns If...

[Karin]

Hi, I need help with coding to hide columns.
I would like to hide all columns that DON'T have "TM" in the first row. If
I do this, can Row 1 also be hidden?

TIA

 

[Karin]

I should have added that, at the moment, the cells without "TM" in them are
empty.

 

[Rick Rothstein]

This macro will that...

Sub HideTMColumns()
Dim C As Range
Dim NonTMcolumns As Range
For Each C In Columns
If Cells(1, C.Column).Value <> "TM" Then
If NonTMcolumns Is Nothing Then
Set NonTMcolumns = C
Else
Set NonTMcolumns = Union(C, NonTMcolumns)
End If
End If
Next
NonTMcolumns.EntireColumn.Hidden = True
Rows(1).Hidden = True
End Sub

 

[Gord Dibben]

At the moment, this macro will hide all columns that are blank in row 1

Will also hide row 1

Sub hide_blank_cols()
Dim c As Range
With ActiveSheet.Rows(1)
Do
Set c = .Find("", LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireColumn.Hidden = True
Loop
End With
End Sub

For later, assuming your data changes and you will have non-blank cells in
row 1 that don't contain "TM"

Sub hide_cols()
Dim ColNdx As Long
Dim LastCol As Long
LastCol = ActiveSheet.UsedRange.Columns.Count
For ColNdx = LastCol To 1 Step -1
If Cells(ColNdx).Value <> "TM" Then
Columns(ColNdx).Hidden = True
End If
Next ColNdx
Rows(1).Hidden = True
End Sub


Gord Dibben MS Excel MVP