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