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
"Peter T" wrote:
> That looks fine but what's the ultimate objective, particularly if that
> address length is 255+
>
> Regards,
> Peter T
>
> "ExcelMonkey" <(E-Mail Removed)> wrote in message
> news:5A6E13AC-F134-484C-BF71-(E-Mail Removed)...
> > 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
> >>
> >> "ExcelMonkey" <(E-Mail Removed)> wrote in message
> >> news:3F58D2A8-76BC-4C33-9AE5-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>
>
|
Similar Threads
