UsedRange

[Guest]

Hello,

An odd thing is happening with Access/Excel automation.

1. I populate a sheet named Data1 and then use code to automatically
generate a Bubble Chart in Excel.

2. If I run this one time, and generate 10 lines of data, then run it a
second time, and generate 9 lines of data, the chart creation function chokes
in loop iteration 10 on the line of code (For i = 2 To xlRange.Rows.Count),
because the USEDRANGE function in the line of code (Set xlRange =
xlBook.Worksheets(strDataSheet).UsedRange) returns 10 vs. 9.

Any ideas?

 

[John Nurick]

Hi David,

UsedRange basically returns the rectangular range containing all the
cells in the worksheet that contain values or have ever been formatted.
Probably the best thing to do is to write code that scans the rows until
it comes to the end of your data, then set xlRange accordingly.