Macros that hide and unhide columns

[lindasf]

Hello,

I have a 161 column spreadsheet. I would like to be able to run a macr
to hide certain (non-contiguous) columns in the spreadsheet
(Unfortunately these columns can not be sorted together so they wil
always be non-contiguous).

In plain English, what I would like to do is look at all the cells i
Row 1 from Column D to EN (e.g. D1:EN1).

For each of those cells that contains a 1, 2 or 4, I would like to hid
the corresponding column.

The end result is that (within the D1:EN1 range) I would only show th
columns that have a “3” in them.

(Of course I would show the A:C columns and the EO:FE columns).

Attached is the file on which I would like to run this macro.

Thank you very much

Attachment filename: calwin medi-cal training analysis-4-13-04.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=51144

 

[Don Guillett]

How about

Sub hidecolumns()
x = InputBox("Number to Show")
For Each c In Range("e1:en1")
If Right(c, 1) <> x Then c.EntireColumn.Hidden = True
Next
End Sub

Sub unhide()
Columns("e:en").Hidden = False
End Sub

 

[Don Guillett]

You can even use this to do by color number where 35 is the light green.

Sub hidecolumns()
x = InputBox("Number of color to Show")
For Each c In Range("e1:en1")
If c.Interior.ColorIndex <> 35 Then c.EntireColumn.Hidden = True
'If Right(c, 1) <> x Then c.EntireColumn.Hidden = True
Next
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

How about

Sub hidecolumns()
x = InputBox("Number to Show")
For Each c In Range("e1:en1")
If Right(c, 1) <> x Then c.EntireColumn.Hidden = True
Next
End Sub

Sub unhide()
Columns("e:en").Hidden = False
End Sub

 

[lindasf]

Don,

Thanks so much! This runs great!

lindas

 

[Gord Dibben]

Try this macro.

Option Compare Text
Sub hidecols()
Set myRange = Range("D1:EN1")
myNum = "*3*"
myRange.EntireColumn.Hidden = False
For Each mycell In myRange.Cells
If Not mycell.Value Like myNum Then
mycell.EntireColumn.Hidden = True
End If
Next mycell
End Sub

Gord Dibben Excel MVP

 

[lindasf]

Don,

One more quick question please ...

(I am obviously a newbee to EXCEL macros).

If I also want to create a macro that simply hides a range of column
(e.g. Column D through EN) how do I write that?

I know that I could do a simple hide without a macro since the column
are contiguous, but it would be nice to be able to run it from a macr
(e.g. avoid all the scrolling).

Thx. much.

lindas

 

[Frank Kabel]

Hi
try
sub foo()
activesheet.columns("D:EN").hidden=True
end sub

 

[Don Guillett]

Look at the unhide macro I sent.

 

[lindasf]

Thank you both very much