auto data source, removing null

[OTWarrior via OfficeKB.com]

I have a chart that is automatically generating a chart based on the data
imported into the spreadsheet, however, the number of rows of data is not the
same, so when the chart generates based on a selection that has more rows
selected than there is data for, it creates a very weird looking chart.

..Columns("A:B").Select
.Charts.Add
.ActiveChart.ChartType = xlColumnClustered
.ActiveChart.SetSourceData Source:=Sheets("Bookings Made").Range
("A1:B24"), PlotBy:=xlColumns

(NB: I am using with statments for ease of use)

as you can see, the range is set to ("A1:B24"), but I need it to be more
dymanic. I tried using ("A:B"), but that pulls the same error (as it is
looking for data which isn't there in B30 for example)

should I use some sort of length checker to see what the last row with data
is? and how would I do it?

 

[Jon Peltier]

You could use

Source:=Sheets("Bookings Made").Range("A1").CurrentRegion

- Jon

 

[OTWarrior via OfficeKB.com]

Just figured out I can use Dcount to count the query, all i need to do is add
one to it.

eg:

((DCount("*", "Query1")) + 1)

..ActiveChart.SetSourceData Source:=Sheets("Bookings received").Range("A1:B" &
((DCount _("*", "Query1")) + 1)), PlotBy:=xlColumns