Limitation on Excel 2003 Non-Contiguous Cells?

[Raeldor]

Hi All,

Is there a limit in Excel 2003 for ranges with non-contiguous cells? I have
code that is looping through doing a union of some columns (limited rows)
and not others, and when it gets past 13 columns, the column count remains
the same even though the cell count is still increasing as new columns are
added.

The behaviour is very strange.

Thanks
Rael

 

[Raeldor]

Hi Again,

I did some more testing on this, and it seems to be when the range becomes
non-contiguous is when this stops working properly. Is there a problem with
unioning non-contiguous ranges? Why would the column count stop increasing?

Thanks
Rael

 

[Raeldor]

Here's some vba code showing the issue...


' add first column
Set myRange = Range("b5:b50")
MsgBox Str(myRange.Columns.Count)
Set myRange = Union(myRange, Range("c5:c50"))
MsgBox Str(myRange.Columns.Count)
Set myRange = Union(myRange, Range("e5:e50"))
MsgBox Str(myRange.Columns.Count)

 

[Andy Pope]

Hi,

You need to use Area to count columns in non-contiguous range.

For Each rngArea In myrange.Areas
lngColCount = lngColCount + rngArea.Columns.Count
Next
MsgBox lngColCount

Cheers
Andy

 

[Jialiang Ge [MSFT]]

Hello Raeldor,

From your post, my understanding on this issue is: when you union multiple
non-adjacent ranges in Excel, the resulting range's column number is not
correct. If I'm off base, please feel free to let me know.

I have tested your vba code and reproduced the issue. Actually, the union
of non-adjacent ranges will create multiple 'Areas' in the Range object.
The code 'myRange.Columns.Count' will only return the column number of the
first Area (myRange.Areas(1)). In order to get the correct column number of
non-adjacent ranges, you need to iterate the Areas and add up the column
numbers. For instance:
Dim i As Integer
Dim count As Integer
For i = 1 To myRange.Areas.count
count = count + myRange.Areas(i).Columns.count
Next
MsgBox Str(count)

You may also refer to the following kb which describes a very similar
issue. The kb talks about Range.EntireRow, but I find that it also applies
to Range.Columns after my test.
http://support.microsoft.com/kb/q108518/

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Raeldor]

Awesome. Thanks guys!

Hi,

You need to use Area to count columns in non-contiguous range.

For Each rngArea In myrange.Areas
lngColCount = lngColCount + rngArea.Columns.Count
Next
MsgBox lngColCount

Cheers
Andy