Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
auto data source, removing null
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
auto data source, removing null
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-increment data source cell references when copying charts | Excel Discussion (Misc queries) | |||
How to auto-increment data source cell references when copying cha | Charts and Charting in Excel | |||
Pivot table format changes if value of source data is null | Excel Discussion (Misc queries) | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions | |||
Pivot Table Auto Update Data Source? | Excel Worksheet Functions |