Selectively hiding columns with a macro

[Guest3731]

Hi - I just posted a question about data presentation - this is a
simplification.

Is it possible to add a pair of buttons using VB that when clicked:

1. hides/shows a contiguous set of columns? the set being defined by
either:

a) an "if" statement that checks for the existence of a piece of
text in a particular row of each column

or

b) a name that is given in advance to that set of columns

I'm hoping to selectively narrow a very wide spreadsheet. If possible
I would want to have this continue to work should I add either new
rows, or new columns.

Any hints?

 

[Don Guillett]

How about using data>filter>autofilter> equal to

 

[Gord Dibben]

Give the set of columns a defined name of mycols

Sub toggle_columns()
Dim rng As Range
Dim Cell As Range
Set rng = ActiveSheet.Range("mycols")
rng.EntireColumn.Hidden = Not rng.EntireColumn.Hidden
End Sub

Toggles hide and unhide with one button.


Gord Dibben MS Excel MVP

 

[Guest3731]

Give the set of columns a defined name of mycols

Sub toggle_columns()
Dim rng As Range
Dim Cell As Range
Set rng = ActiveSheet.Range("mycols")
rng.EntireColumn.Hidden = Not rng.EntireColumn.Hidden
End Sub

Toggles hide and unhide with one button.

These suggestions are great, thank you.

Allow me to ask one further question - is there a way of designating a
set of columns in advance such that someone can come along later and
add a column to the spreadsheet and have the macro still work? I'm
trying to make something that someone other than myself will be using,
and they will completely freak out if I tell them to change the name
of a range, alter a macro, etc.

I am guessing that if I want this functionality I would have to switch
the macro around to incorporate some sort of "if" test -

Many thanks -

 

[Don Guillett]

Give us details of the layout and the possibilities.

 

[Guest3731]

Give us details of the layout and the possibilities.

Fundamentally, I'm trying to put 3 worksheets-worth of data into one
spreadsheet, the 3 sets of data stretching out horizontally from left
to right. The boss wants to see 3 different "projections" or
hypothetical situations, based on 3 different percentages being
applied to a set of numbers. I want to try and use only one
spreadsheet, partly because I am not yet up to speed on pivot tables,
which have been suggested to me, and partly because if I create 3
different spreadsheets, someone's going to come along later and de-
synchronize them.

I've got names in Column A (e.g. "Joe"). There's a couple of columns
of unchanging, irrelevant textual data in Columns B and C, pertaining
to the name in Column A. Columns D and E have numeric data in them,
they're two different types of debts "Joe" has incurred in a given
year. There might be 6-10 pairs of columns like this to the right of
D and E, each for Joe, each for a different year. Joe might be
getting a discount on his debts. Therefore, the boss wants to see
what Joe's numbers, for those same years, would look like were a
couple of different discounts applied.

So my idea is to set up, say, Columns M-Z as exact duplicates of D-L
(or whatever), and Columns AA-LL as duplicates of M-Z, the only
differences being a discount multiplier applied to the two new sets of
columns. If I had a button, I could hide the columns I'm not using,
but I wouldn't have to have three separate spreadsheets.

The things that would change, potentially, are that extra pairs of
"year" columns could be added, and extra "Joe" rows would be added.

ShouldI give you more information than this? Trying to be complete.

Thanks very much -

 

[Don Guillett]

Instead of me trying to reconstruct it may be easier for you to send the
file to my address below along with snippets of these emails.