| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide column if it contains text
I want to hide all columns containing text and leave the numeric columns
unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
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 "Jodie" wrote: I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
That macro will not hide any columns composed of both numbers **and** text.
-- Rick (MVP - Excel) "Mike H" wrote in message ... 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 "Jodie" wrote: I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
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.Colu mns(X)), "") _ Like "*[!0-9]*" Then WS.Columns(X).Hidden = True Next End Sub -- Rick (MVP - Excel) "Jodie" wrote in message ... I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
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 --------------- Jacob Skaria "Jodie" wrote: I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
What if I want to do this for all worksheets in a workbook?
-- Thank you, Jodie "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jodie" wrote: I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
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 --------------- Jacob Skaria "Jodie" wrote: What if I want to do this for all worksheets in a workbook? -- Thank you, Jodie "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jodie" wrote: I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide column if it contains text
Thank you Jacob. This works, but is there a way to ignore rows 1 & 2 when
determining if there is text in the column? -- Thank you, Jodie "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jodie" wrote: What if I want to do this for all worksheets in a workbook? -- Thank you, Jodie "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jodie" wrote: I want to hide all columns containing text and leave the numeric columns unhidden. Is there a macro that can be written for that? -- Thank you, Jodie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hide Column If Usedrange of Column ISBLANK Follow-up | Excel Discussion (Misc queries) | |||
| hide one column skip two column and unhide one of 11 | Excel Programming | |||
| Hide Column Based on Partial Text Match in Header | Excel Programming | |||
| How do I hide text beyond the last column in Excel? | Excel Discussion (Misc queries) | |||
| Hide Column - With text in cell | Excel Programming | |||
Linear Mode
