In row 7 of Columns G thru R (12 columns) of a worksheet called "output", I have dates for January 31st thru Dec 31st (in a given year). Based on an input cell that contains one of these 12 dates, I want to hide all columns (in that year) that are on or before that date. So, I could start with column G and hide it as well as up to 11 more columns immediately to its right. Example, if the input date were 7/31, I would want to hide the first seven columns and keep the last five. I guess column G would always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very much!

Also, somewhere on the web, I seem to recall a site with loads of examples of EXCEL macros for doing common things. Often I can figure out how to adapt one of these. Can anyone help me to rediscover it?

Dean

Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).Enti reColumn.Hidden = True


--
Regards,
Tom Ogilvy


"Dean" wrote:

In row 7 of Columns G thru R (12 columns) of a worksheet called "output", I have dates for January 31st thru Dec 31st (in a given year). Based on an input cell that contains one of these 12 dates, I want to hide all columns (in that year) that are on or before that date. So, I could start with column G and hide it as well as up to 11 more columns immediately to its right. Example, if the input date were 7/31, I would want to hide the first seven columns and keep the last five. I guess column G would always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very much!

Also, somewhere on the web, I seem to recall a site with loads of examples of EXCEL macros for doing common things. Often I can figure out how to adapt one of these. Can anyone help me to rediscover it?

Dean

This looks awesomely simple, Tom, but I don't remember what else I need
before or after it to actually perform the task. Can you help?

Dean

"Tom Ogilvy" wrote in message
...
Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).Enti reColumn.Hidden = True


--
Regards,
Tom Ogilvy


"Dean" wrote:

In row 7 of Columns G thru R (12 columns) of a worksheet called "output",
I have dates for January 31st thru Dec 31st (in a given year). Based on
an input cell that contains one of these 12 dates, I want to hide all
columns (in that year) that are on or before that date. So, I could start
with column G and hide it as well as up to 11 more columns immediately to
its right. Example, if the input date were 7/31, I would want to hide
the first seven columns and keep the last five. I guess column G would
always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very much!

Also, somewhere on the web, I seem to recall a site with loads of
examples of EXCEL macros for doing common things. Often I can figure out
how to adapt one of these. Can anyone help me to rediscover it?

Dean

OK, I think I found the problem - there was a right parenthesis missing

Thanks, Tom for a very clever single - line solution.


"Dean" wrote in message
...
This looks awesomely simple, Tom, but I don't remember what else I need
before or after it to actually perform the task. Can you help?

Dean

"Tom Ogilvy" wrote in message
...
Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).Enti reColumn.Hidden = True


--
Regards,
Tom Ogilvy


"Dean" wrote:

In row 7 of Columns G thru R (12 columns) of a worksheet called
"output", I have dates for January 31st thru Dec 31st (in a given year).
Based on an input cell that contains one of these 12 dates, I want to
hide all columns (in that year) that are on or before that date. So, I
could start with column G and hide it as well as up to 11 more columns
immediately to its right. Example, if the input date were 7/31, I would
want to hide the first seven columns and keep the last five. I guess
column G would always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very much!

Also, somewhere on the web, I seem to recall a site with loads of
examples of EXCEL macros for doing common things. Often I can figure
out how to adapt one of these. Can anyone help me to rediscover it?

Dean

Sub ABC()
Dim i as Long
' determine the number of months to hide
i = Month(Range("B2").Value)
' unhide all columns in G to R
Columns("G").Resize(,12).Entirecolumn.Hidden = False
' no hide the appropriate columns
columns("G").Resize(,i).EntireColumn.Hidden = True
End sub

Obviously change Range("B2") to the appropriate reference.

--
Regards,
Tom Ogilvy

"Dean" wrote:

This looks awesomely simple, Tom, but I don't remember what else I need
before or after it to actually perform the task. Can you help?

Dean

"Tom Ogilvy" wrote in message
...
Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).Enti reColumn.Hidden = True


--
Regards,
Tom Ogilvy


"Dean" wrote:

In row 7 of Columns G thru R (12 columns) of a worksheet called "output",
I have dates for January 31st thru Dec 31st (in a given year). Based on
an input cell that contains one of these 12 dates, I want to hide all
columns (in that year) that are on or before that date. So, I could start
with column G and hide it as well as up to 11 more columns immediately to
its right. Example, if the input date were 7/31, I would want to hide
the first seven columns and keep the last five. I guess column G would
always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very much!

Also, somewhere on the web, I seem to recall a site with loads of
examples of EXCEL macros for doing common things. Often I can figure out
how to adapt one of these. Can anyone help me to rediscover it?

Dean

I guess out posts were crossing. Am I wrong or was your one line solution
enough? It seems to work fine, now. Newbie that I am, when the compiler
produced a red font, I didn't think to check the obvious, a missing right
paren.

D
"Tom Ogilvy" wrote in message
...
Sub ABC()
Dim i as Long
' determine the number of months to hide
i = Month(Range("B2").Value)
' unhide all columns in G to R
Columns("G").Resize(,12).Entirecolumn.Hidden = False
' no hide the appropriate columns
columns("G").Resize(,i).EntireColumn.Hidden = True
End sub

Obviously change Range("B2") to the appropriate reference.

--
Regards,
Tom Ogilvy

"Dean" wrote:

This looks awesomely simple, Tom, but I don't remember what else I need
before or after it to actually perform the task. Can you help?

Dean

"Tom Ogilvy" wrote in message
...
Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).Enti reColumn.Hidden =
True


--
Regards,
Tom Ogilvy


"Dean" wrote:

In row 7 of Columns G thru R (12 columns) of a worksheet called
"output",
I have dates for January 31st thru Dec 31st (in a given year). Based
on
an input cell that contains one of these 12 dates, I want to hide all
columns (in that year) that are on or before that date. So, I could
start
with column G and hide it as well as up to 11 more columns immediately
to
its right. Example, if the input date were 7/31, I would want to hide
the first seven columns and keep the last five. I guess column G
would
always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very
much!

Also, somewhere on the web, I seem to recall a site with loads of
examples of EXCEL macros for doing common things. Often I can figure
out
how to adapt one of these. Can anyone help me to rediscover it?

Dean

6 of one, half dozen of another.

I actually thought you wanted to know how to do

Sub MySub()

End Sub

so I broke it into pieces to boot and added some comments

(hard to comment a single line with multiple parts)

No the original line is fine with the addition of the right paren and the
correction for word wrap in the post

--
Regards,
Tom Ogilvy


"Dean" wrote:

I guess out posts were crossing. Am I wrong or was your one line solution
enough? It seems to work fine, now. Newbie that I am, when the compiler
produced a red font, I didn't think to check the obvious, a missing right
paren.

D
"Tom Ogilvy" wrote in message
...
Sub ABC()
Dim i as Long
' determine the number of months to hide
i = Month(Range("B2").Value)
' unhide all columns in G to R
Columns("G").Resize(,12).Entirecolumn.Hidden = False
' no hide the appropriate columns
columns("G").Resize(,i).EntireColumn.Hidden = True
End sub

Obviously change Range("B2") to the appropriate reference.

--
Regards,
Tom Ogilvy

"Dean" wrote:

This looks awesomely simple, Tom, but I don't remember what else I need
before or after it to actually perform the task. Can you help?

Dean

"Tom Ogilvy" wrote in message
...
Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).Enti reColumn.Hidden =
True


--
Regards,
Tom Ogilvy


"Dean" wrote:

In row 7 of Columns G thru R (12 columns) of a worksheet called
"output",
I have dates for January 31st thru Dec 31st (in a given year). Based
on
an input cell that contains one of these 12 dates, I want to hide all
columns (in that year) that are on or before that date. So, I could
start
with column G and hide it as well as up to 11 more columns immediately
to
its right. Example, if the input date were 7/31, I would want to hide
the first seven columns and keep the last five. I guess column G
would
always be hidden with this algorithm.

Can anyone help me with the syntax of such a macro? Thank you very
much!

Also, somewhere on the web, I seem to recall a site with loads of
examples of EXCEL macros for doing common things. Often I can figure
out
how to adapt one of these. Can anyone help me to rediscover it?

Dean