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

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

Set rng1 = sht.Cells.SpecialCells(xlFormulas)

--
Jim
"ExcelMonkey" wrote in message
...
| 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(xlFormu las)
| 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
|
| "ExcelMonkey" wrote in message
| ...
| 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(xlFormu las)
| ContRange = rng1.Address
| Debug.Print sht.Name
| Debug.Print ContRange
| Debug.Print Len(ContRange)
| Next
|
| End Sub
|
| Thanks
|
| EM
|
|
|