This code should do what you want
It will prompt the user for the venue they want to see and hide the others
Not case sensitive. No need to select your range.
But it assumes a Table format for your data: No Gaps in Rows OR Columns

Private Sub Macro1( )
Dim MyStr as String, MyRng as Rang
MyStr = inputBox("Type in a Venue Number(1, 2 or 3)"
if IsNumeric( MyStr) then
If CInt(MyStr) < 1 Or CInt(MyStr)3 Then
MsgBox "must be a number(1, 2 or 3)": Exit Su
Els
MsgBox "must be a number(1, 2 or 3)": Exit Su
End If
Set MyRng = Range("A1").CurrentRegio
For Each c in MyRng
If c.Column = 1 Then Goto NextC
If c.Column = MyRng.Columns.Count Then Goto NextC
If c.Column.Hidden = True Then Goto NextC

If Not Cint(Mystr) = 1 Then
if InStr(1, Cstr(c.value), "Venue1",1)0 Then c.EntireColumn.Hidden = True
If Not Cint(Mystr) = 2 Then
if InStr(1, Cstr(c.value), "Venue2",1)0 Then c.EntireColumn.Hidden = True
If Not Cint(Mystr) = 3 Then
if InStr(1, Cstr(c.value), "Venue3",1)0 Then c.EntireColumn.Hidden = True
NextC
Nex
End Sub

----- wrote: ----

Sorry if I wasn't clear, I didn't want to go into too much detail and bore
everyone - so here's another attempt to explain what I'm trying to do

At work we have an excel file which is used to show information about
future plays we are going to put on

Each column represents a different play with the rows containing data about
these plays i.e. play name, director, designer, venue et

We have 3 different venues at work (Venue 1, Venue 2, Venue 3) and some
people who use the chart are only interested in plays which are taking place
in one of the venues - so I need a macro which will hide all columns where
the venue = Venue 2 or Venue 3 thus leaving all the plays which will happen
in Venue 1 (plus 2 other macros for the other venues, but I'm guessing I can
just copy the macro and alter the appropriate words

Just to complicate things the venue cell could contain the respective words
as part of a sentanc

And yet another complication is that first and last column in the table are
used for the row title, the first row is fine as it will allways be column A but
the table is updated each week and regularly has columns added and
remove

Hope that this makes sens

Many Thank

And


-----Original Message----
Andy
I hope this will help. Your post wasn't clear.
Sub Find_me(
Cells.Find(What:="20", After:=ActiveCell, LookIn:=xlFormulas
LookAt:=xlWhole).Activat
Selection.EntireColumn.Hidden = Tru
End Su
Charle
--
Message posted from http://www.ExcelForum.com