Dynamic chart with macro function

**noraps** · 07-11-2013, 03:46 AM

Hello!
I have this charts that is based on data from a web query. When I press a macro button there are different parameters I will need to put in, for example hox many hours back I wanna see (this is a chart on previous weather). The thing is, when its 24 hours it fits my chart perfectly, but when it is, say, 6 hours, the chart will still use the data from all the same cells as when it was 24, hence my chart will have a lot of "REF!" points up to 6 hours ago.

How will I be able to make my chart dynamic so I can change the time parameters as it suits the situation?

If something came off as unclear, just ask. Thanks in advance!

**Richard Buttrey** · 07-11-2013, 03:49 AM

Hi,

One way would be to create a dynamic range name for your chart data which automatically adjusts to the number of relevant rows and then use that range name as the source for your chart.

**codeslizer** · 07-11-2013, 03:50 AM

It would help us understand better if you could attach a copy of your work or macro. Just remove any sensitive data and feed in the basic dummy values so that we can understand the calculations.

**noraps** · 07-11-2013, 04:00 AM

Hello codeslizer, this is the macro:

Sub Refresh()
'
' Refresh Macro
'

'

ActiveWorkbook.RefreshAll
Sheets("Used data").Select

MsgBox "Here is your METAR data from " & Range("V6").Value & "!"

End Sub

and here is the temperature at the moment with 24 hours as one of the paramters:
Air Temp Dew Point
110350 9 8
110250 9 8
110150 9 8
110050 9 8
102350 9 7
102250 9 8
102150 10 8
102120 10 8
102050 11 8
102020 11 8
101950 11 8
101920 11 8
101850 12 8
101820 12 8
101750 13 8
101720 13 8
101650 13 8
101620 13 8
101550 13 9
101520 13 8
101450 13 8
101420 13 8
101350 14 8
101320 14 8
101250 14 8
101220 14 9
101150 14 9
101120 14 9
101050 14 8
101020 14 9
100950 14 8
100920 13 8
100850 13 8
100820 13 8
100750 13 8
100720 13 8
100650 12 9
100620 12 8
100550 12 8
100520 11 8
100450 11 8
100420 10 8

Thanks Richrad, I wil try that!

**noraps** · 07-11-2013, 04:01 AM

Hello codeslizer, this is the macro:

Sub Refresh()
'
' Refresh Macro
'

'

ActiveWorkbook.RefreshAll
Sheets("Used data").Select

MsgBox "Here is your METAR data from " & Range("V6").Value & "!"

End Sub

and here is the temperature at the moment with 24 hours as one of the paramters:
Air Temp Dew Point
110350 9 8
110250 9 8
110150 9 8
110050 9 8
102350 9 7
102250 9 8
102150 10 8
102120 10 8
102050 11 8
102020 11 8
101950 11 8
101920 11 8
101850 12 8
101820 12 8
101750 13 8
101720 13 8
101650 13 8
101620 13 8
101550 13 9
101520 13 8
101450 13 8
101420 13 8
101350 14 8
101320 14 8
101250 14 8
101220 14 9
101150 14 9
101120 14 9
101050 14 8
101020 14 9
100950 14 8
100920 13 8
100850 13 8
100820 13 8
100750 13 8
100720 13 8
100650 12 9
100620 12 8
100550 12 8
100520 11 8
100450 11 8
100420 10 8

Thanks Richrad, I wil try that!