Macro for hiding columns based on header names

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
Hello - I need help in creating macros which will hide specific columns in a worksheet -not just by reference.
For instance, if i had a worksheet with the headers 'apples' 'oranges' 'bananas' 'hamburgers' 'hot dogs' etc. and macro buttons on the top that state 'SHOW COLUMNS of FRUIT only' and 'SHOW COLUMNS of MEAT only'. The way I have it now - I have set macros up manually to hide specific columns - but if you add additional columns in between, it skews the macro results. Can a macro be created to list the names of the column headers so wherever they are moved - the macro will still identify it and hide or unhide it? this may be an Excel 101 question - but not for me.

Thanks..
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JohnG

Board Regular
Joined
Feb 18, 2002
Messages
165
you might want to make the headers a range name then you can have a macro refer to them.
IE if you had a header called 'APPLES'
the coumn id would be got as follows
ColID=range(APPLES).columns
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
You can name a column.
Select column, click column bar, name column by selecting drop down at left of formula bar, type "apples".

Column (range in this case) is named apples.

Code:

Sub Macro1()
' Macro recorded 10/24/2002 by chas
Range("apples").Select
Selection.EntireColumn.Hidden = True
End Sub
 

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
'Excellent' [Mr Burns sitting behind desk]
so.. if i had columns listed as:
apples/oranges/tomatos/bananas/strawberries/grapes

and in Macro 1 I want only oranges & grapes columns visible I would:

Sub Macro1()
Columns("A:ZZ").Select
Selection.EntireRow.Hidden = False
Range("apples","tomatos":"strawberries").Select
Selection.EntireColumn.Hidden = True
End Sub

*the first command makes sure all columns are visible to start with.
does ("apples","tomatos":"strawberries") make sense?
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
No, Range() will not work that way

Sub Macro1()
' Macro recorded 10/24/2002 by chas
Columns("A:ZZ").Select
Selection.EntireColumn.Hidden = False
Range("apples").Select
Selection.EntireColumn.Hidden = True
Range("tomatoes").Select
Selection.EntireColumn.Hidden = True
Range("strawberries").Select
Selection.EntireColumn.Hidden = True
End Sub

Is a bit much but works. I may have a better way at home, look tomorrow if not pressed for time.
 

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
Chas - thanks. one more question about Range().
If I have a column called 'apples' (say column c) and i want to select c5:c8 how would you write that? I cant seem to find any help in xcel about it.

sorry - i should prob carry this to another topic..
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top