Worksheet.Range returns an error: First and last addresses must be in the same worksheet

Oct 21, 2011 at 7:21 PM

I get the error "First and last addresses must be in the same worksheet" when I send in two IXLCell objects for the range.  The issue is that both of these IXLCell objects are on the same worksheet.  I have noticed that the error only seems to occur when I am trying to get ranges further down on the sheet then where any existing data is at.  If I add formatting to additional cells further down the sheet, the range call seems to work just fine.

I was using a previous version and was not having this problem but when updating to the latest to resolve another issue this error started to occur.

Any thoughts on why this is happening or how to prevent it?  I attempted to fix this by adding some dummy data if the row(s) I was going to return in the range call returned zero cells in the .CellsUsed(true) call, but that didn't seem to have any affect.  Inserting new rows also does not help the issue.

Oct 21, 2011 at 7:29 PM

Interesting.  I changed the call to send in the row and column numbers to the range instead and it worked just fine.  I could see the start and end cells all said they were part of the same worksheet, so I'm not sure why this fixed the problem...

Coordinator
Oct 21, 2011 at 8:26 PM

Please post a way to recreate the error. It'll probably be a quick fix.  I'm almost done implementing auto filters (full support) and will create a release soon.

Oct 24, 2011 at 12:57 PM

Actually, it looks like it is an issue on my side.  We are dealing with template worksheets that we are copying multiple times to do our edits in.  We get the positions for editing using named ranges and then doing some ranges using offsets from the initial named ranges.  (For things like repeating tables.)  The IXLCell objects we are using for getting the new range objects are actually on the initial template sheet, not the one we are editing, so this error makes sense.   I assumed the error meant that the first and last were on different sheets, but I guess it meant that the first and last are both on different sheets that the sheet that .Range is being called upon.

It's still a bit odd that it doesn't occur on every call to .Range, but at least the error is explainable and understandable.

Oddly enough, I'm having trouble reproducing in a new solution. 

Coordinator
Oct 24, 2011 at 4:57 PM

Now that you mention it, I don't think the current rule helps anyone. I'll change it so you can pass any two cell objects as long as they compose a valid range.

Thanks for the feedback.

Coordinator
Oct 26, 2011 at 6:11 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.