Hide column if it contains text

[Jodie]

I want to hide all columns containing text and leave the numeric columns
unhidden. Is there a macro that can be written for that?

 

[Mike H]

Hi,

Try this

Sub Lime()
For col = 1 To ActiveSheet.UsedRange.Columns.Count
If WorksheetFunction.Count(Columns(col)) = 0 Then
Columns(col).EntireColumn.Hidden = True
End If
Next
End Sub

Mike

 

[Rick Rothstein]

That macro will not hide any columns composed of both numbers **and** text.

 

[Rick Rothstein]

Give this macro a try...

Sub HideNonNumericColumns()
Dim X As Long, WS As Worksheet
Set WS = ActiveSheet
For X = 1 To WS.UsedRange.Columns.Count
If Join(WorksheetFunction.Transpose(WS.UsedRange.Columns(X)), "") _
Like "*[!0-9]*" Then WS.Columns(X).Hidden = True
Next
End Sub

 

[Jacob Skaria]

Hi Jodie

If you dont have formulas in the range...the below will do..Will hide
columns which contain text..

Sub ColHide()
For Each Col In ActiveSheet.UsedRange.Columns
If WorksheetFunction.CountIf(Columns(Col.Column), "?*") <> 0 Then
Col.EntireColumn.Hidden = True
End If
Next
End Sub

If this post helps click Yes

 

[Jodie]

What if I want to do this for all worksheets in a workbook?

 

[Jacob Skaria]

Hi Jodie

Try the below

Sub ColHide()
For Each ws in Worksheets
With ws
For Each Col In ws.UsedRange.Columns
If WorksheetFunction.CountIf(ws.Columns(Col.Column), "?*") <> 0 Then
Col.EntireColumn.Hidden = True
End If
Next
End With
Next
End Sub

If this post helps click Yes

 

[Jodie]

Thank you Jacob. This works, but is there a way to ignore rows 1 & 2 when
determining if there is text in the column?