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?

How about using data>filter>autofilter> equal to

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message
news:90ae4dc3-1f11-481f-8431-03123284d05e@r66g2000hsg.googlegroups.com...
> 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?

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

On Thu, 28 Aug 2008 08:12:00 -0700 (PDT), Guest3731
<notconfusedaboutthattoday@gmail.com> wrote:

>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?

On Aug 28, 2:36 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> 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 -

Give us details of the layout and the possibilities.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message
news:25fcc675-0522-4982-b48a-d12566594504@c58g2000hsc.googlegroups.com...
> On Aug 28, 2:36 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>> 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 -
>

On Aug 29, 7:13 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> 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 -

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message
news:e0caf962-701f-45f1-aeb4-8b858d3b7629@k30g2000hse.googlegroups.com...
> On Aug 29, 7:13 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>> 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 -