ExcelBanter - Character limit in Range Method

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Character limit in Range Method (https://www.excelbanter.com/excel-programming/432984-character-limit-range-method.html)

ExcelMonkey

Character limit in Range Method

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

ExcelMonkey

Character limit in Range Method

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

"ExcelMonkey" wrote:

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

JLGWhiz[_2_]

Character limit in Range Method

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

"ExcelMonkey" wrote in message
...
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

"ExcelMonkey" wrote:

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

ExcelMonkey

Character limit in Range Method

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$5 2,$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(xlForm ulas)
x = a.Address
Debug.Print x
Debug.Print "-----------------------------"

Debug.Print Len(x)
End Sub

Thanks

EM

"JLGWhiz" wrote:

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

"ExcelMonkey" wrote in message
...
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

"ExcelMonkey" wrote:

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

Rick Rothstein

Character limit in Range Method

Why does the followihg work

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

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

--
Rick (MVP - Excel)

"ExcelMonkey" wrote in message
...
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

ExcelMonkey

Character limit in Range Method

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

"ExcelMonkey" wrote:

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$5 2,$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(xlForm ulas)
x = a.Address
Debug.Print x
Debug.Print "-----------------------------"

Debug.Print Len(x)
End Sub

Thanks

EM

"JLGWhiz" wrote:

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

"ExcelMonkey" wrote in message
...
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

"ExcelMonkey" wrote:

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

ExcelMonkey

Character limit in Range Method

See my third post.

EM

"Rick Rothstein" wrote:

Why does the followihg work

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

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

--
Rick (MVP - Excel)

"ExcelMonkey" wrote in message
...
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

Peter T

Character limit in Range Method

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

"ExcelMonkey" wrote in message
...
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$5 2,$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(xlForm ulas)
x = a.Address
Debug.Print x
Debug.Print "-----------------------------"

Debug.Print Len(x)
End Sub

Thanks

EM

"JLGWhiz" wrote:

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

"ExcelMonkey" wrote in message
...
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

"ExcelMonkey" wrote:

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

Rick Rothstein

Character limit in Range Method

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 (MVP - Excel)

"Peter T" <peter_t@discussions wrote in message
...
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

"ExcelMonkey" wrote in message
...
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$5 2,$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(xlForm ulas)
x = a.Address
Debug.Print x
Debug.Print "-----------------------------"

Debug.Print Len(x)
End Sub

Thanks

EM

"JLGWhiz" wrote:

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

"ExcelMonkey" wrote in message
...
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

"ExcelMonkey" wrote:

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

Peter T

Character limit in Range Method

"Rick Rothstein" wrote in message

If I remember correctly, there is an 8192 limit on the number of
non-contiguous areas that a Union can hold;

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

Peter T

Character limit in Range Method

"ExcelMonkey" wrote in message

To create the CustomRng I might create a Collection object of ranges.

I can't imagine why you'd want to do that.

But I believe I still end up dealing with the 255 limit

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

ExcelMonkey

Character limit in Range Method

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(xlform ulas).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(xlform ulas).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

"Peter T" wrote:

"ExcelMonkey" wrote in message

To create the CustomRng I might create a Collection object of ranges.

I can't imagine why you'd want to do that.

But I believe I still end up dealing with the 255 limit

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

Peter T

Character limit in Range Method

First, I see a typo in my previous post

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

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?

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.

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

Regards,
Peter T

"ExcelMonkey" wrote in message
...
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(xlform ulas).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(xlform ulas).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

"Peter T" wrote:

"ExcelMonkey" wrote in message

To create the CustomRng I might create a Collection object of ranges.

I can't imagine why you'd want to do that.

But I believe I still end up dealing with the 255 limit

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

Peter T

Character limit in Range Method

All times are GMT +1. The time now is 07:32 PM.