Character limit in Range Method

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

Why does the followihg work

x = Range("$A$1", "$A$2").Address
Debug.Print x

But not the following:

x = Range("$A$1", "$A$2", "$A$3").Address
Debug.Print x

Thanks

EM
 
Is it because the Range Method can only take two ranges? I know that this
works:

x = Union(Range("$A$1"), Range("$A$2"), Range("$A$3")).Address

I am assuming the Union function has a limit as well.

EM
 
I don't believe the problem is as much linits as it is convention. I guess
I am missing the logic in using the address string to return the address
string. It would make more sense if:

x = AciveSheet.Range(Cells(1, 1), Cells(2, 1)).Address
MsgBox x
 
I am trying to understand the limits of the Range Method. I was just using
that as an example. When I put formulas in every second row from C1:C100 and
run the code in EXAMPLE 1 below, I get the following print out in my
Immediate Window:

$C$2,$C$4,$C$6,$C$8,$C$10,$C$12,$C$14,$C$16,$C$18,$C$20,$C$22,$C$24,$C$26,$C$28,$C$30,$C$32,$C$34,$C$36,$C$38,$C$40,$C$42,$C$44,$C$46,$C$48,$C$50,$C$52,$C$54,$C$56,$C$58,$C$60,$C$62,$C$64,$C$66,$C$68,$C$70,$C$72,$C$74,$C$76,$C$78,$C$80,$C$82,$C$84,$C$86
-----------------------------
253

As you can see the not all the cells are picked up. I am assuming that this
is because the Range method has a 255 character limit and the commas are
included in the string.

I want to dig into a way to get around this char limit in the range method.
Do you know of any workarounds?

EXAMPLE 1
Sub Thing()
Dim a As Range
Dim x As String

Set a = Worksheets("Sheet1").UsedRange.SpecialCells(xlFormulas)
x = a.Address
Debug.Print x
Debug.Print "-----------------------------"

Debug.Print Len(x)
End Sub

Thanks

EM
 
Why does the followihg work
x = Range("$A$1", "$A$2").Address
Debug.Print x
Click to expand...

I don't think this line of code is doing what you think it is doing, mainly
because you picked a bad range (adjacent cells) to show what this line is
doing. Use this range instead...

x = Range("$A$1", "$A$23").Address

When you print x out, you will see this...

$A$1:$A$23

Notice the range contains 23 cells, not the two I think you thought it would
contain. That is why this doesn't work for you (notice I changed the cell
addresses so range is not a contiguous one)...

x = Range("$A$1", "$A$23", "$A$45").Address

VB doesn't know what you are trying to do with a third cell in there. The
way to do what I think you are trying to do is to not provide *separate*
arguments; but, rather, provide a single text string containing the three
cell addresses within it as the argument, like this...

x = Range("$A$1,$A$23,$A$45").Address
 
I dug into the archives and realized that you and I discussed this very topic
about a year ago. You directed me to the following link:

http://support.microsoft.com/kb/213841

However ther did not seem to be a workaround for pass long character strings
to the Range method.

Thanks

EM
 
See my third post.

EM

Rick Rothstein said:
I don't think this line of code is doing what you think it is doing, mainly
because you picked a bad range (adjacent cells) to show what this line is
doing. Use this range instead...

x = Range("$A$1", "$A$23").Address

When you print x out, you will see this...

$A$1:$A$23

Notice the range contains 23 cells, not the two I think you thought it would
contain. That is why this doesn't work for you (notice I changed the cell
addresses so range is not a contiguous one)...

x = Range("$A$1", "$A$23", "$A$45").Address

VB doesn't know what you are trying to do with a third cell in there. The
way to do what I think you are trying to do is to not provide *separate*
arguments; but, rather, provide a single text string containing the three
cell addresses within it as the argument, like this...

x = Range("$A$1,$A$23,$A$45").Address
Click to expand...
 
Indeed, the address is limited to absolute maximum 255 characters including
commas. You can increase the number of areas returned with
range.address(0,0) to strip absolute $ characters. Also remove any $ when
creating a large multi-area range with a string address around 255 or a bit
more with the $'s

If dealing with a range that might exceed 255, return the address by looping
areas. Similarly don't try to create a range with an address longer than
255, use Union. Note, Union becomes exponentially slower with increasing
areas, eventually slows down to a crawl, despite that I'm not aware of an
area limit other than resources.

Regards,
Peter T
 
If I remember correctly, there is an 8192 limit on the number of
non-contiguous areas that a Union can hold; but you are right... things
would slow down to a crawl way before you reached this limit. The key is to
process the contents of the Union well below this limit (perhaps 100 areas),
clear the Union and continue from there. For example, below is the code
framework for deleting, hiding, etc. rows of data for a given condition. The
RowsToDelete range is used to hold the Union. This block...

If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete xlShiftUp
Set RowsToDelete = Nothing
End If

within the For-Next loop is used to perform the delete when the Union has
accumulated 100 areas and then reset the range for the next group of Unions.
Here is the overall code framework...

Dim X As Long
Dim LastRow As Long
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range

Const DataStartRow As Long = 1
Const UnionColumn As String = "A"
Const SheetName As String = "Sheet1"

On Error Goto Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
' <<Set your test condition here>>
If .Cells(X, UnionColumn).Value = 0 Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, UnionColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, UnionColumn))
End If
If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete xlShiftUp
Set RowsToDelete = Nothing
End If
End If
Next
End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete xlShiftUp
End If

Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True
 
Rick Rothstein said:
If I remember correctly, there is an 8192 limit on the number of
non-contiguous areas that a Union can hold;
Click to expand...

8192 areas is the limit with Special cells. Unioning areas beyond that is
limited by only resouces, though "time" would more likely be a practical
limit.

Union is OK up to a few hundred areas, perhaps more with modern systems.
Also there are a few ways to considerably speed up Union for making a few
thousand areas (lot of helper code).

Once a large multi area range has been made, up to say 4000, it's not
unweildy to work with.

Regards,
Peter T
 
I like the .Address(0,0) approach. This will help. Where I am going with
all of this is that I may want to create some custom ranges.
I wouldn't be using the SpecialCells() Method to create these custom ranges.
I would be building them from scratch.

I want to end up with something as follows:

For each cell in CustomRange
'do something
Next

To create the CustomRng I might create a Collection object of ranges. Then
I would pass the items in the Collection to the custom range
via a function. But I believe I still end up dealing with the 255 limit.
Paste the code below into a module.

Public CellCol As Collection
'***************************
Sub Thing()
Dim TheRange As Range
Dim Counter As Double
Call LoadCollection

Counter = 0

Set TheRange = CustomRange()
Debug.Print TheRange.Address(0, 0)
Debug.Print Len(TheRange.Address(0, 0))
Debug.Print CellCol.Count '# of collection items

'check and see how many collection items made it into range
For Each cell In TheRange
Counter = Counter + 1
Next

'this should be < # of collection items
'if you breached 255 char limit
MsgBox ("Counter =" & Counter)

End Sub
'*********************************
Sub LoadCollection()
'Add custom list to collection
Set CellCol = New Collection
CellCol.Add Range("Sheet1!A5")
CellCol.Add Range("Sheet1!A10")
CellCol.Add Range("Sheet1!A25")
CellCol.Add Range("Sheet1!A30")
CellCol.Add Range("Sheet1!A80")
CellCol.Add Range("Sheet1!A90")
CellCol.Add Range("Sheet1!A180")
CellCol.Add Range("Sheet1!A280")
CellCol.Add Range("Sheet1!A380")
CellCol.Add Range("Sheet1!A480")
CellCol.Add Range("Sheet1!A580")
CellCol.Add Range("Sheet1!A680")
CellCol.Add Range("Sheet1!A780")
CellCol.Add Range("Sheet1!A880")
CellCol.Add Range("Sheet1!A980")
CellCol.Add Range("Sheet1!A1080")
CellCol.Add Range("Sheet1!A1180")
CellCol.Add Range("Sheet1!A1280")
CellCol.Add Range("Sheet1!A1380")
CellCol.Add Range("Sheet1!A1480")
CellCol.Add Range("Sheet1!A1580")
CellCol.Add Range("Sheet1!A1680")
CellCol.Add Range("Sheet1!b5")
CellCol.Add Range("Sheet1!b10")
CellCol.Add Range("Sheet1!b25")
CellCol.Add Range("Sheet1!b30")
CellCol.Add Range("Sheet1!b80")
CellCol.Add Range("Sheet1!b90")
CellCol.Add Range("Sheet1!b180")
CellCol.Add Range("Sheet1!b280")
CellCol.Add Range("Sheet1!b380")
CellCol.Add Range("Sheet1!b480")
CellCol.Add Range("Sheet1!b580")
CellCol.Add Range("Sheet1!b680")
CellCol.Add Range("Sheet1!b780")
CellCol.Add Range("Sheet1!b880")
CellCol.Add Range("Sheet1!b980")
CellCol.Add Range("Sheet1!b1080")
CellCol.Add Range("Sheet1!b1180")
CellCol.Add Range("Sheet1!b1280")
CellCol.Add Range("Sheet1!b1380")
CellCol.Add Range("Sheet1!b1480")
CellCol.Add Range("Sheet1!b1580")
CellCol.Add Range("Sheet1!b1680")
CellCol.Add Range("Sheet1!c1780")
CellCol.Add Range("Sheet1!c1880")
CellCol.Add Range("Sheet1!c1980")
CellCol.Add Range("Sheet1!d2080")
CellCol.Add Range("Sheet1!d2280")
CellCol.Add Range("Sheet1!d2480")
CellCol.Add Range("Sheet1!d2680")
CellCol.Add Range("Sheet1!d2880")
End Sub
'**********************************
Function CustomRange() As Range
Dim rngUnion2 As Range
Dim rngUnion1 As Range
Dim FinalArray As Variant
Dim rng2 As Range

ReDim FinalArray(0 To CellCol.Count)
Counter = 1
For Each rng2 In CellCol
Set rng3 = Range(rng2.Address(0, 0))
Set rngUnion1 = rng3.Areas(1)
For Each rngArea In rng3.Areas
'Combine addresses in collection item.
'A1, A2, A3 B1 becomes:
'A1:A3, B1 in colleciton item
Set rngUnion1 = Union(rngUnion1, rngArea)
Next
'Pass results to aray
FinalArray(Counter - 1) = rngUnion1.Address(0, 0)
'Debug.Print rngUnion1.Address
Counter = Counter + 1
Next

previousunion = ""
For x = 1 To CellCol.Count
If x = 1 Then
Set rngUnion1 = Range(FinalArray(x - 1))
Else
'The failure should occur here
't = Len(previousunion)
Set rngUnion1 = Union(Range(previousunion), Range(FinalArray(x - 1)))
'Debug.Print Union(Range(previousunion), Range(FinalArray(x -
1))).Address
If Err.Number <> 0 Then
Set rngUnion2 = rngUnion1
Set rngUnion1 = Range(FinalArray(x - 1))
End If
'On Error GoTo 0
End If
previousunion = rngUnion1.Address(0, 0)
Next
'Debug.Print rngUnion1.Address

Set CustomRange = rngUnion1

End Function
'********************************
Thanks

EM
 
ExcelMonkey said:
To create the CustomRng I might create a Collection object of ranges.
Click to expand...

I can't imagine why you'd want to do that.
But I believe I still end up dealing with the 255 limit
Click to expand...

Avoid ever getting into the situation where the 255 limit will hit you,
plenty of other ways, even with a large multi-area range.

Rule of thumb, if the range has up to 10 / 15 areas (97-2003 / 2007)
anywhere on the sheet you can use address to create or read (without $ to
create or address(0,0) to return). If unknown or more areas use Union and
loop areas to return.

Regards,
Peter T
 
I just realized that I have not fully understood the issue here. But I think
I have it now.

The 255 character quirk of the Range method only really affects the address
property of the range returned. For example, if you use an existing
collection like:

?Worksheets("Sheet1").UsedRange.SpecialCels(xlformulas).Address

You may get a truncated address string. As you noted you can expand the
length of the string returned by gong:

?Worksheets("Sheet1").UsedRange.SpecialCels(xlformulas).Address(0,0)

Or you can loop through the areas.

It is important to note that even though the address string itself may be
truncated due to this quirk, I believe the collection itself is completely
intact (i.e. all items are included in the collection).

Also if you decide to build the collection yourself as in:
CustomRange.Address or CustomRange.Address(0,0)

The items in this custom collection are intact as well even though the
address returned may be truncated.

I have been assuming all along that the collection itself was not intact and
rendered incomplete. Hence I did not see any value in creating custom ranges
if they were flawed. But they are not.

Am I correct?

Thanks

EM
 
First, I see a typo in my previous post

should read

Rule of thumb, if the range has up to 15 / 10 areas (97-2003 / 2007)

I have been assuming all along that the collection itself was not intact
and
rendered incomplete. Hence I did not see any value in creating custom
ranges
if they were flawed. But they are not.

Am I correct?
Click to expand...

If you mean is it possible and viable to create and use a range that would
have an address very considerably longer than 255, were it possible to
return it, absolutely. As you've pointed out it's merely the address that
gets truncated.

However I wouldn't describe a single range object that comprises multiple
areas a "collection".
The 255 character quirk of the Range method only really affects the
address
property of the range returned.
Click to expand...

The 255 limitation also applies if you want to create a range

Regards,
Peter T
 
First, I see a typo in my previous post

should read

Rule of thumb, if the range has up to 15 / 10 areas (97-2003 / 2007)

I have been assuming all along that the collection itself was not intact
and
rendered incomplete. Hence I did not see any value in creating custom
ranges
if they were flawed. But they are not.

Am I correct?
Click to expand...

If you mean is it possible and viable to create and use a range that would
have an address very considerably longer than 255, were it possible to
return it, absolutely. As you've pointed out it's merely the address that
gets truncated.

However I wouldn't describe a single range object that comprises multiple
areas a "collection".
The 255 character quirk of the Range method only really affects the
address
property of the range returned.
Click to expand...

The 255 limitation also applies if you want to create a range

Regards,
Peter T
 
Back
Top