Range too complex
 

Thanks for the suggestion. I've gone down the sorting data first route and
it seems to work fine.

"Dave Peterson" wrote in message
...
.specialcells has trouble if the resulting range has more than 8192 areas.

Maybe you could sort your data first to clump the cells that should be
cleared,
then sort back to the original order.

Or maybe you could go in smaller steps looping 16000 rows at a time. In
the
worst case, you'd grab every other row--or 8000 areas.

Nirmal Singh wrote:

Thanks for the input, guys.

I did try looping through the worksheet as suggested by Gary's Student,
but
it took too long.

I have also tried the resize suggestion, but that still raises the same
error code. I think it must be the number of discrete visible cells that
remain after the autofilter.

Anyway, what I've finally done is insert a formula in a blank column,
selecting the values from column AN that I require. I then paste these
back
into column AN.

Nirmal

"JMB" wrote in message
...
I don't think it likes the intersect method (maybe it does not like the
fact
there are multiple non-contiguous intersection points).
W/SpecialCells,
you
shouldn't need it anyway.

If you don't want to include the first row of the filter range (if they
are
headers):

Sub test()
Dim MyRange As Range

Range("A1").AutoFilter Field:=25, _
Criteria1:="<0", Operator:=xlAnd, Criteria2:="<1"
With ActiveSheet.AutoFilter.Range
.Offset(1, 0).Resize(.Rows.Count - 1, _
.Columns.Count).SpecialCells(xlCellTypeVisible).Cl earContents
End With
If ActiveSheet.AutoFilterMode Then _
ActiveSheet.AutoFilter
End Sub

Otherwise:
Sub test2()

Range("A1").AutoFilter Field:=25, _
Criteria1:="<0", Operator:=xlAnd, Criteria2:="<1"
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTy peVisible).ClearContents
If ActiveSheet.AutoFilterMode Then _
ActiveSheet.AutoFilter
End Sub

"Nirmal Singh" wrote:

I want to clear the contents of column AN where the corresponding row
in
column Y is not 0 or 1.

I am using the following code

ActiveSheet.Range("A1").Select
Selection.AutoFilter Field:=25, Criteria1:="<0", Operator:=xlAnd,
Criteria2:="<1"
Set myRange = Application.Intersect(ActiveSheet.Range("AN2:AN655 35"),
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTy peVisible))
myRange.ClearContents

I get an error message "Range is too complex".

How can I resolve this?

Nirmal




--

Dave Peterson