Thread: 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

Assume the input date is in cell B2:

columns("G").Resize(,month(Range("B2").Value).EntireColumn.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" <TomOgilvy@discussions.microsoft.com> wrote in message
news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
> Assume the input date is in cell B2:
>
> columns("G").Resize(,month(Range("B2").Value).EntireColumn.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" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
> > Assume the input date is in cell B2:
> >
> > columns("G").Resize(,month(Range("B2").Value).EntireColumn.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" <whooshbopbang4@adelphia.net> wrote in message
news:EbSdndF0s7urKhDZnZ2dnUVZ_v6dnZ2d@adelphia.com...
> 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" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
>> Assume the input date is in cell B2:
>>
>> columns("G").Resize(,month(Range("B2").Value).EntireColumn.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" <TomOgilvy@discussions.microsoft.com> wrote in message
news:6FED2C96-F501-4A09-BAC5-92CCD685AFC3@microsoft.com...
> 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" <TomOgilvy@discussions.microsoft.com> wrote in message
>> news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
>> > Assume the input date is in cell B2:
>> >
>> > columns("G").Resize(,month(Range("B2").Value).EntireColumn.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" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:6FED2C96-F501-4A09-BAC5-92CCD685AFC3@microsoft.com...
> > 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" <TomOgilvy@discussions.microsoft.com> wrote in message
> >> news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
> >> > Assume the input date is in cell B2:
> >> >
> >> > columns("G").Resize(,month(Range("B2").Value).EntireColumn.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 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" <TomOgilvy@discussions.microsoft.com> wrote in message
news:F856F81A-9438-4FDA-B820-FA4EA10790A5@microsoft.com...
>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" <TomOgilvy@discussions.microsoft.com> wrote in message
>> news:6FED2C96-F501-4A09-BAC5-92CCD685AFC3@microsoft.com...
>> > 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" <TomOgilvy@discussions.microsoft.com> wrote in message
>> >> news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
>> >> > Assume the input date is in cell B2:
>> >> >
>> >> > columns("G").Resize(,month(Range("B2").Value).EntireColumn.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
>> >>
>> >>
>> >>
>>
>>
>>

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


"Dean" <whooshbopbang4@adelphia.net> wrote in message
news:nLedndM3L_yfzhPZnZ2dnUVZ_rydnZ2d@adelphia.com...
> 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" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:F856F81A-9438-4FDA-B820-FA4EA10790A5@microsoft.com...
> >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" <TomOgilvy@discussions.microsoft.com> wrote in message
> >> news:6FED2C96-F501-4A09-BAC5-92CCD685AFC3@microsoft.com...
> >> > 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" <TomOgilvy@discussions.microsoft.com> wrote in message
> >> >> news:361C0397-2A3C-4924-B613-6768695A16BA@microsoft.com...
> >> >> > Assume the input date is in cell B2:
> >> >> >
> >> >> > columns("G").Resize(,month(Range("B2").Value).EntireColumn.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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>