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

"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

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

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

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

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

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

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

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