Autofilter with Range problem
 

Hi all,

I have the following code on deleting rows using autofilter. However,
strange problem appears.

I have no idea on that. :(

Any help would be grateful.
Thanks.

Range that will be deleted:
-------------
$Q$73:$Q$77
$Q$2:$Q$3
$1:$1,$309:$65536


Code:
-------------
For h = 1 To arrayH.Count
For i = 1 To arrayQ.Count
ActiveSheet.Range("A1").AutoFilter Field:=cccCol
Criteria1:=arrayQ(i), Operator:=xlAnd
ActiveSheet.Range("A1").AutoFilter Field:=acctCol
Criteria1:=arrayH(h)
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1
1).SpecialCells(xlCellTypeVisible)
Set rng2
rng.Columns(17).SpecialCells(xlCellTypeVisible)
If Not rng2 Is Nothing An
Round(Application.WorksheetFunction.Sum(rng2), 0) = 0 Then
Debug.Print rng2.Address
Application.WorksheetFunction.Sum (rng2) & "
& Round(Application.WorksheetFunction.Sum(rng2), 0)
rng2.EntireRow.Delete
End If
End With
Next i
Next

--
Message posted from http://www.ExcelForum.com

Autofilter with Range problem
 

what is the strange problem that appears?
-----Original Message-----
Hi all,

I have the following code on deleting rows using
autofilter. However, a
strange problem appears.

I have no idea on that. :(

Any help would be grateful.
Thanks.

Range that will be deleted:
-------------
$Q$73:$Q$77
$Q$2:$Q$3
$1:$1,$309:$65536


Code:
-------------
For h = 1 To arrayH.Count
For i = 1 To arrayQ.Count
ActiveSheet.Range("A1").AutoFilter Field:=cccCol,
Criteria1:=arrayQ(i), Operator:=xlAnd
ActiveSheet.Range("A1").AutoFilter Field:=acctCol,
Criteria1:=arrayH(h)
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
Set rng2 =
rng.Columns(17).SpecialCells(xlCellTypeVisible)
If Not rng2 Is Nothing And
Round(Application.WorksheetFunction.Sum(rng2), 0) = 0 Then
Debug.Print rng2.Address
Application.WorksheetFunction.Sum (rng2) & " "
& Round(Application.WorksheetFunction.Sum(rng2), 0)
rng2.EntireRow.Delete
End If
End With
Next i
Next h


---
Message posted from http://www.ExcelForum.com/

.