Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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...tm#sensitivity --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "chris" wrote in message ... This is case Sensitive: Do this if you dont want it to be Private Sub Macro1( ) Dim MyStr as String, Val as String 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 Next 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 it Private Sub Macro1( ) '<< this should be provided Dim MyStr as String MyStr = "YourValue" For Each c in Selection if InStr(1, Cstr(c.value), MyStr)0 Then c.EntireColumn.Hidden = True Next End Sub '<< this should be provided ----- 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide Columns
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 -----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).Activate Selection.EntireColumn.Hidden = True End Sub Charles --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to hide columns with zero values? | Excel Discussion (Misc queries) | |||
Macro to Hide columns | Excel Discussion (Misc queries) | |||
Macro to hide the row if both columns E & F are blank in that row. | Excel Worksheet Functions | |||
Need a macro to hide certain columns | Excel Discussion (Misc queries) | |||
macro to hide columns | Excel Worksheet Functions |