Macro to find columns to hide
 

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

Macro to find columns to hide
 

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

Macro to find columns to hide
 

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

Macro to find columns to hide
 

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

Macro to find columns to hide
 

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

Macro to find columns to hide
 

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

Macro to find columns to hide
 

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

Macro to find columns to hide
 

This detail will come in handy, Tom. Thanks. Do you know of a good site
with lots of sample macros for doing common tasks? I know I saw one
somewhere.

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
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

Macro to find columns to hide
 

http://www.cpearson.com/excel.htm
--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
This detail will come in handy, Tom. Thanks. Do you know of a good site
with lots of sample macros for doing common tasks? I know I saw one
somewhere.

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
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