hide columns based on header macro
 

Hi, I need a macro that can loop through a workbook and based on the headers
hide columns. So I could use a dropbox list, select a heading and the
headings on the list not selected (four possible headers for the list and 30
worksheets) would be hidden.

I learning but am not good at VB and will appreciate any help.


Thanks,


Todd

hide columns based on header macro
 

Try something like the following, making adjustments to fit your layout
Assume your combobox is on Sheet(1)
Attach the following code to your combobox
Assign the combobox linkcell as "W1"

Sub HideColumns()
Dim sh as worksheet
Dim c as range
For each sh in Worksheets ' loop thru all worksheets
For each c in Range("A1:D1") ' assuming this is where your headers are
If c.value=Sheet(1).Range("W1")
c.EntireColumn.Hidden=False
Else
c.EntireColumn.Hidden=True
End If
Next c
Next sh
End Sub


"Todd L." wrote:

Hi, I need a macro that can loop through a workbook and based on the headers
hide columns. So I could use a dropbox list, select a heading and the
headings on the list not selected (four possible headers for the list and 30
worksheets) would be hidden.

I learning but am not good at VB and will appreciate any help.


Thanks,


Todd

hide columns based on header macro
 

Thanks so much,

I am having an error at "If c.Value = Sheet(1).Range("W1") " which says
there is an expected then or go to. I put a then at the end of the statement
and I get
"sub or function not defined". What else do I need to do?

Todd


Sub HideColumns()
Dim sh As Worksheet
Dim c As Range
For Each sh In Worksheets ' loop thru all worksheets
For Each c In Range("A1:D1") ' assuming this is where your headers are
If c.Value = Sheet(1).Range("W1") Then
c.EntireColumn.Hidden = False
Else
c.EntireColumn.Hidden = True
End If
Next c
Next sh
End Sub


"gocush" wrote:

Try something like the following, making adjustments to fit your layout
Assume your combobox is on Sheet(1)
Attach the following code to your combobox
Assign the combobox linkcell as "W1"

Sub HideColumns()
Dim sh as worksheet
Dim c as range
For each sh in Worksheets ' loop thru all worksheets
For each c in Range("A1:D1") ' assuming this is where your headers are
If c.value=Sheet(1).Range("W1")
c.EntireColumn.Hidden=False
Else
c.EntireColumn.Hidden=True
End If
Next c
Next sh
End Sub


"Todd L." wrote:

Hi, I need a macro that can loop through a workbook and based on the headers
hide columns. So I could use a dropbox list, select a heading and the
headings on the list not selected (four possible headers for the list and 30
worksheets) would be hidden.

I learning but am not good at VB and will appreciate any help.


Thanks,


Todd

hide columns based on header macro
 

sorry
change the following line:

If c.Value = Sheet(1).Range("W1") Then

to

If c.Value = Sheets("YourSheetName").Range("W1") Then

Change "YourSheetName" to the name of your sheet where you have
range("W1") storing the value from the dropdown.

Another note: If you are using a dropdown made from the FORMS toolbar, then
the value stored in the linkcell ("W2") will not be a text value as in your
header.
Instead, it will be a number: If the user selects the third option in the
dropbox
which might be "Blue" if your headers are colors, then Range("W2") will have
the value 3 rather than "Blue". The 3 would then have to be converted back
to "Blue" in order to compare it to your headers.

On the other hand, if your dropdown was created form the CONTROL TOOLBOX, it
will store the actual text "Blue" in W3

If you have a FORMS dropdown,
use the following

Option Explicit

Sub HideColumns()
Dim sh As Worksheet
Dim c As Range
Dim Sel As String
Sel = Application.Index(Sheets("Sheet1").Range("A1:D1"), _
Sheets("Sheet1").Range("W1"))
For Each sh In Worksheets ' loop thru all worksheets
For Each c In Range("A1:D1") ' assuming this is where your headers are
If c.Value = Sel Then
c.EntireColumn.Hidden = False
Else
c.EntireColumn.Hidden = True
End If
Next c
Next sh
End Sub


"Todd" wrote:

Thanks so much,

I am having an error at "If c.Value = Sheet(1).Range("W1") " which says
there is an expected then or go to. I put a then at the end of the statement
and I get
"sub or function not defined". What else do I need to do?

Todd


Sub HideColumns()
Dim sh As Worksheet
Dim c As Range
For Each sh In Worksheets ' loop thru all worksheets
For Each c In Range("A1:D1") ' assuming this is where your headers are
If c.Value = Sheet(1).Range("W1") Then
c.EntireColumn.Hidden = False
Else
c.EntireColumn.Hidden = True
End If
Next c
Next sh
End Sub


"gocush" wrote:

Try something like the following, making adjustments to fit your layout
Assume your combobox is on Sheet(1)
Attach the following code to your combobox
Assign the combobox linkcell as "W1"

Sub HideColumns()
Dim sh as worksheet
Dim c as range
For each sh in Worksheets ' loop thru all worksheets
For each c in Range("A1:D1") ' assuming this is where your headers are
If c.value=Sheet(1).Range("W1")
c.EntireColumn.Hidden=False
Else
c.EntireColumn.Hidden=True
End If
Next c
Next sh
End Sub


"Todd L." wrote:

Hi, I need a macro that can loop through a workbook and based on the headers
hide columns. So I could use a dropbox list, select a heading and the
headings on the list not selected (four possible headers for the list and 30
worksheets) would be hidden.

I learning but am not good at VB and will appreciate any help.


Thanks,


Todd

hide columns based on header macro
 

Sub HideColumns()
Dim sh as worksheet
Dim c as range
For each sh in Worksheets ' loop thru all worksheets
For each c in sh.Range("A1:D1") ' assuming this is where your headers are
If c.value = Sheets(1).Range("W1").Value Then
c.EntireColumn.Hidden=False
Else
c.EntireColumn.Hidden=True
End If
Next c
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Todd" wrote in message
...
Thanks so much,

I am having an error at "If c.Value = Sheet(1).Range("W1") " which says
there is an expected then or go to. I put a then at the end of the
statement
and I get
"sub or function not defined". What else do I need to do?

Todd


Sub HideColumns()
Dim sh As Worksheet
Dim c As Range
For Each sh In Worksheets ' loop thru all worksheets
For Each c In Range("A1:D1") ' assuming this is where your headers are
If c.Value = Sheet(1).Range("W1") Then
c.EntireColumn.Hidden = False
Else
c.EntireColumn.Hidden = True
End If
Next c
Next sh
End Sub


"gocush" wrote:

Try something like the following, making adjustments to fit your layout
Assume your combobox is on Sheet(1)
Attach the following code to your combobox
Assign the combobox linkcell as "W1"

Sub HideColumns()
Dim sh as worksheet
Dim c as range
For each sh in Worksheets ' loop thru all worksheets
For each c in Range("A1:D1") ' assuming this is where your headers
are
If c.value=Sheet(1).Range("W1")
c.EntireColumn.Hidden=False
Else
c.EntireColumn.Hidden=True
End If
Next c
Next sh
End Sub


"Todd L." wrote:

Hi, I need a macro that can loop through a workbook and based on the
headers
hide columns. So I could use a dropbox list, select a heading and the
headings on the list not selected (four possible headers for the list
and 30
worksheets) would be hidden.

I learning but am not good at VB and will appreciate any help.


Thanks,


Todd