Help with VBA if/then statement

[Jameo]

Hi all,

I have several dynamic named ranges (day, hour station1, station2 etc.) in my exel spreadsheet.

I also have the following code to plot, and arrange a chart:

    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("Histograms").Range("I21")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Book1!day"
    ActiveChart.SeriesCollection(1).Values = "=Book1!Station1"
    ActiveChart.SeriesCollection(1).Name = "=Book1!name1"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Histograms"
 
Dim RngToCover As Range
Dim ChtOb As ChartObject
Set RngToCover = ActiveSheet.Range("a1:h20")
Set ChtOb = ActiveChart.Parent
ChtOb.Height = RngToCover.Height ' resize
ChtOb.Width = RngToCover.Width ' resize
ChtOb.Top = RngToCover.Top ' reposition
ChtOb.Left = RngToCover.Left ' reposition

My problem is that dependant on the query, some of the dynamic ranges are empty. If they are, then I do not want to plot the chart. Is there a way I can use an if/then statement to:

If - The named range has data populated.
Then - run the above code,
Else if - move on to the next dynamic range (and complete the same checks)
End if

Any help would be great.

Thanks in advance

 

[texasalynn]

you can add an if statement, so my test the range name: abc_list

If Application.WorksheetFunction.CountA(Range("abc_list")) = 0 Then
MsgBox "Range is empty"
Exit Sub
End If

 

[Jameo]

Excellent stuff, thanks. I'll give this a go today. Next step is to get the appropriate data exported automatically from Access, and also clear the data on export/import (leaving the dynamic ranges intact), and add a line to the top of the above to clear any previous graphs plotted on the spreadsheet.

Cheers for your help