Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is Range For Chart AutoFilter Range VBA? | Excel Discussion (Misc queries) | |||
Problem with Autofilter | Excel Discussion (Misc queries) | |||
Autofilter problem | Excel Worksheet Functions | |||
autofilter problem | Excel Worksheet Functions | |||
Now problem with autofilter | Excel Programming |