Contiguous Ranges and 255 Char Limit Constraint

[ExcelMonkey]

I know I can pass all the contiguous ranges of cells with formulas as follows
below. However this suffers from the 255 char limit. That is, if the range
is greater than 255 then the cell address that is passed to the ContRange
variable will be truncated. How do you get around this?

Sub test2()
Dim sht As Worksheet
Dim ContRange As String

For Each sht In ThisWorkbook.Worksheets
Set rng1 = sht.Range("a1:iv65536").Cells.SpecialCells(xlFormulas)
ContRange = rng1.Address
Debug.Print sht.Name
Debug.Print ContRange
Debug.Print Len(ContRange)
Next

End Sub

Thanks

EM

 

[Peter T]

dim rArea as Range
For each rArea in rng1.Areas

do something with rArea,
but don't bother rebuilding a 255+ address because it'll fail when you try
to use it (depending on what you are doing)

Regards,
Peter T

 

[ExcelMonkey]

Excellent Point Peter. This works:

Sub test2()
Dim sht As Worksheet
Dim ContRange As String

For Each sht In ThisWorkbook.Worksheets
ContRange = ""
Counter = 0
Set rng1 = sht.Range("a1:iv65536").Cells.SpecialCells(xlFormulas)
For Each myArea In rng1.Areas
If Counter = 0 Then
ContRange = myArea.Address
Else
ContRange = ContRange & "," & myArea.Address
End If
Counter = Counter + 1
Next
Debug.Print sht.Name
Debug.Print ContRange
Next

End Sub

 

[Peter T]

That looks fine but what's the ultimate objective, particularly if that
address length is 255+

Regards,
Peter T

 

[Jim Rech]

Set rng1 = sht.Range("a1:iv65536").Cells.SpecialCells(xlFormulas)

A small point but this could be a little shorter and work in Excel 2007 too:

Set rng1 = sht.Cells.SpecialCells(xlFormulas)

--
Jim
| Excellent Point Peter. This works:
|
| Sub test2()
| Dim sht As Worksheet
| Dim ContRange As String
|
| For Each sht In ThisWorkbook.Worksheets
| ContRange = ""
| Counter = 0
| Set rng1 = sht.Range("a1:iv65536").Cells.SpecialCells(xlFormulas)
| For Each myArea In rng1.Areas
| If Counter = 0 Then
| ContRange = myArea.Address
| Else
| ContRange = ContRange & "," & myArea.Address
| End If
| Counter = Counter + 1
| Next
| Debug.Print sht.Name
| Debug.Print ContRange
| Next
|
| End Sub
|
|
| "Peter T" wrote:
|
| > dim rArea as Range
| > For each rArea in rng1.Areas
| >
| > do something with rArea,
| > but don't bother rebuilding a 255+ address because it'll fail when you
try
| > to use it (depending on what you are doing)
| >
| > Regards,
| > Peter T
| >
| > | > >I know I can pass all the contiguous ranges of cells with formulas as
| > >follows
| > > below. However this suffers from the 255 char limit. That is, if the
| > > range
| > > is greater than 255 then the cell address that is passed to the
ContRange
| > > variable will be truncated. How do you get around this?
| > >
| > > Sub test2()
| > > Dim sht As Worksheet
| > > Dim ContRange As String
| > >
| > > For Each sht In ThisWorkbook.Worksheets
| > > Set rng1 = sht.Range("a1:iv65536").Cells.SpecialCells(xlFormulas)
| > > ContRange = rng1.Address
| > > Debug.Print sht.Name
| > > Debug.Print ContRange
| > > Debug.Print Len(ContRange)
| > > Next
| > >
| > > End Sub
| > >
| > > Thanks
| > >
| > > EM
| >
| >
| >

 

[ExcelMonkey]

Effectively I want to be able to isolate each contiguous range on each sheet.
I then want to be able to test to see if a specific cell falls within one of
those ranges (True/False). Given that the Range("a1:iv65536")) will never
exceed 255, I should not run into any 255 char limits. Given that each
myArea In rng1.Areas will be contiguous, they too will not exceed the 255
limit. I agree that the joining them in the string variable will potentially
exceed 255. However I will use the Split function to send them all
separately to a collection object or array and only deal with separately.

Regards

RK