Macro to hide Columns

[Andy Ward]

I'm trying to write a Macro that will cycle through each cell in a row (except
the first and last column of the table) and hide the entire column if the cell
doesn't contain a certain word, the word may be on it's own or within a
sentance.

I can't use the sort command as the table contains many merged cells etc

Can any one give me some pointers as to where to start - I have very limited
programming knowledge - have done basic programming but not for a
couple of years or in VBA.

Many Thanks

Andy

 

[Charles]

Andy,

I hope this will help. Your post wasn't clear.



Sub Find_me()

Cells.Find(What:="20", After:=ActiveCell, LookIn:=xlFormulas
LookAt:=xlWhole).Activate
Selection.EntireColumn.Hidden = True
End Sub


Charle

 

[chris]

I don't know how your data is set up so your gonna have to manually select what range you want processed
i don't recommend you select the entire row if you dont have to, but it will work either way

create a new macro and put this code in i

Private Sub Macro1( ) '<< this should be provide
Dim MyStr as Strin
MyStr = "YourValue
For Each c in Selection
if InStr(1, Cstr(c.value), MyStr)>0 Then c.EntireColumn.Hidden = True
Nex
End Sub '<< this should be provide

----- Andy Ward wrote: ----

I'm trying to write a Macro that will cycle through each cell in a row (except
the first and last column of the table) and hide the entire column if the cell
doesn't contain a certain word, the word may be on it's own or within a
sentance

I can't use the sort command as the table contains many merged cells et

Can any one give me some pointers as to where to start - I have very limited
programming knowledge - have done basic programming but not for a
couple of years or in VBA

Many Thank

And

 

[chris]

this will only work once you need to add a FindNext loop for it to find all value

----- Charles > wrote: ----

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

 

[chris]

This is case Sensitive:
Do this if you dont want it to b

Private Sub Macro1( )
Dim MyStr as String, Val as Strin
MyStr = "YOURVALUE" '<< All Caps
For Each c in Selection
Val = UCase( Cstr(c.value))
if InStr(1, Val, MyStr)>0 Then c.EntireColumn.Hidden = True
Nex
End Sub

----- chris wrote: ----

I don't know how your data is set up so your gonna have to manually select what range you want processed
i don't recommend you select the entire row if you dont have to, but it will work either way

create a new macro and put this code in i

Private Sub Macro1( ) '<< this should be provide
Dim MyStr as Strin
MyStr = "YourValue
For Each c in Selection
if InStr(1, Cstr(c.value), MyStr)>0 Then c.EntireColumn.Hidden = True
Nex
End Sub '<< this should be provide

----- Andy Ward wrote: ----

I'm trying to write a Macro that will cycle through each cell in a row (except
the first and last column of the table) and hide the entire column if the cell
doesn't contain a certain word, the word may be on it's own or within a
sentance

I can't use the sort command as the table contains many merged cells et

Can any one give me some pointers as to where to start - I have very limited
programming knowledge - have done basic programming but not for a
couple of years or in VBA

Many Thank

And

 

[David McRitchie]

InStr has another operand where you can choose
(default) 0 or vbBinaryCompare
1 or vbTextCompare
in your test it would be the third operand but if you started
in a specified position it would be the fourth operand.

Saves one instruction.
This would be a bit faster than insuring that both
are uppercase before comparing. See VBE HELP , also perhaps
http://www.mvps.org/dmcritchie/excel/strings.htm#sensitivity

 

[Guest]

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 etc

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 sentance

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
removed

Hope that this makes sense

Many Thanks

Andy

 

[chris]

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

----- [email protected] 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