auto data source, removing null

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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200707/1

You could use

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"OTWarrior via OfficeKB.com" u33269@uwe wrote in message
news:749dee750b5d5@uwe...
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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200707/1

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200707/1