|  | Selectively hiding columns with a macro | | |
| | | Guest3731 |  |
|  Posted: Thu Aug 28, 2008 3:12 pm Post subject: Selectively hiding columns with a macro |  |
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 | |
| | Posted: Thu Aug 28, 2008 3:12 pm Post subject: Re: Selectively hiding columns with a macro | |
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...
| Quote: | 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? |
|
| |
| | | Gord Dibben | |
| | Posted: Thu Aug 28, 2008 5:36 pm Post subject: Re: Selectively hiding columns with a macro | |
| |  | |
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:
| Quote: | 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 | |
| | Posted: Fri Aug 29, 2008 10:13 am Post subject: Re: Selectively hiding columns with a macro | |
| | | |
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...
| Quote: | 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 -
|
|
| |
| | | Guest3731 | |
| | Posted: Fri Aug 29, 2008 11:04 am Post subject: Re: Selectively hiding columns with a macro | |
On Aug 28, 2:36 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
| Quote: | 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 - |
| |
| | | Guest3731 | |
| | Posted: Fri Aug 29, 2008 2:35 pm Post subject: Re: Selectively hiding columns with a macro | |
| | | |
On Aug 29, 7:13 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
| Quote: | 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 | |
| | Posted: Fri Aug 29, 2008 5:26 pm Post subject: Re: Selectively hiding columns with a macro | |
| | | |
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...
| Quote: | 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 - |
|
| |
|
|
