Hi,

Thanks in advance for any help.

I have a chart that display sales data for the months of the year, what I am
currently doing is hiding for instance all the months that currently have no
data (so for example if we are in June, then July December will be hidden.
Is there way that I can create a macro that will automatically hide the
months that has no data?

So when we reach July, July becomes unhidden and August €“ December remains
hidden?


Many thanks
Andy

Yes, but I don't know the exact commands. You need to work with the x axis
(or y depending on your arrangement.) And be able to change your minimum and
maximum values. You will need to set those values to the result of
converting a date to a number (a number of type long) I don't know if your
data is being displayed for a calendar year, a fiscal year or X months in the
past, but you can use today's date to pull out the last month to be included,
and either programmatically determine the start date, or have it as something
that you have to change each "year" however your year is determined.

Based on your discussion, I would do this:
The end date (max value) would be determined by taking the first day of this
month and year add one month to this month(Don't just increment the month
number, actually add a month and not a thirty day month as February doesn't
have 30.) then subtract one day. This would give you the last day of this
month. Or maybe you just need to say the last date is today, whatever.. :)

My concern in all of this, is the chart's ability to provide only data for a
given month, not splitting some data into one month and other data in the
next month without realizing it.

Anyway, it's doable, and I can't say I have helped you much. Sorry. *smirk*

"Andy the yeti" wrote:

Hi,

Thanks in advance for any help.

I have a chart that display sales data for the months of the year, what I am
currently doing is hiding for instance all the months that currently have no
data (so for example if we are in June, then July December will be hidden.
Is there way that I can create a macro that will automatically hide the
months that has no data?

So when we reach July, July becomes unhidden and August €“ December remains
hidden?


Many thanks
Andy

probably my fault for not explaining well enough, I was planning to control
the chart by hiding or unhidding columns in the chart source tab so if column
E is June then hide anything to the right of that...

By hidding the columns I can then control what is seen in the chart, I just
don't know how to automaticaly hide the appropirate columns

Thanks

"GB" wrote:

Yes, but I don't know the exact commands. You need to work with the x axis
(or y depending on your arrangement.) And be able to change your minimum and
maximum values. You will need to set those values to the result of
converting a date to a number (a number of type long) I don't know if your
data is being displayed for a calendar year, a fiscal year or X months in the
past, but you can use today's date to pull out the last month to be included,
and either programmatically determine the start date, or have it as something
that you have to change each "year" however your year is determined.

Based on your discussion, I would do this:
The end date (max value) would be determined by taking the first day of this
month and year add one month to this month(Don't just increment the month
number, actually add a month and not a thirty day month as February doesn't
have 30.) then subtract one day. This would give you the last day of this
month. Or maybe you just need to say the last date is today, whatever.. :)

My concern in all of this, is the chart's ability to provide only data for a
given month, not splitting some data into one month and other data in the
next month without realizing it.

Anyway, it's doable, and I can't say I have helped you much. Sorry. *smirk*

"Andy the yeti" wrote:

Hi,

Thanks in advance for any help.

I have a chart that display sales data for the months of the year, what I am
currently doing is hiding for instance all the months that currently have no
data (so for example if we are in June, then July December will be hidden.
Is there way that I can create a macro that will automatically hide the
months that has no data?

So when we reach July, July becomes unhidden and August €“ December remains
hidden?


Many thanks
Andy

Ohh. I think that's much easier...

I've not used the chart source tab to control charts persay. If I
understand correctly though... Okay.. Pausing for a moment...

If you mean the source data that is entered by going to Chart-Source Data,
then the data range tab, where it shows the range used on the worksheet(s) to
display the graph, then we are talking about the same business.

I would recommend, recording a macro where you change the range only. Then,
establish whatever comparison you need to tell if the particular column
should be used.

Join the code from those two things, so that your end result is that the
data range of your chart, does not include the areas that are to be left off.

If you are talking about hiding say column F just by making it not visible
on the screen, that is another thing, but that would not prevent the data
from being viewed in a chart.


"Andy the yeti" wrote:

probably my fault for not explaining well enough, I was planning to control
the chart by hiding or unhidding columns in the chart source tab so if column
E is June then hide anything to the right of that...

By hidding the columns I can then control what is seen in the chart, I just
don't know how to automaticaly hide the appropirate columns

Thanks

"GB" wrote:

Yes, but I don't know the exact commands. You need to work with the x axis
(or y depending on your arrangement.) And be able to change your minimum and
maximum values. You will need to set those values to the result of
converting a date to a number (a number of type long) I don't know if your
data is being displayed for a calendar year, a fiscal year or X months in the
past, but you can use today's date to pull out the last month to be included,
and either programmatically determine the start date, or have it as something
that you have to change each "year" however your year is determined.

Based on your discussion, I would do this:
The end date (max value) would be determined by taking the first day of this
month and year add one month to this month(Don't just increment the month
number, actually add a month and not a thirty day month as February doesn't
have 30.) then subtract one day. This would give you the last day of this
month. Or maybe you just need to say the last date is today, whatever.. :)

My concern in all of this, is the chart's ability to provide only data for a
given month, not splitting some data into one month and other data in the
next month without realizing it.

Anyway, it's doable, and I can't say I have helped you much. Sorry. *smirk*

"Andy the yeti" wrote:

Hi,

Thanks in advance for any help.

I have a chart that display sales data for the months of the year, what I am
currently doing is hiding for instance all the months that currently have no
data (so for example if we are in June, then July December will be hidden.
Is there way that I can create a macro that will automatically hide the
months that has no data?

So when we reach July, July becomes unhidden and August €“ December remains
hidden?


Many thanks
Andy