What is the better way to scan cells after autofiler

[Guest]

I have a spreadsheet containing over 10K-30K rows of data. I need to provide
some calculation at a set of user defined criteria at runtime of macros.
I currently use the following 2 structions to filter out a set of data
(rows) based on 2 user defined criteria: AccountID and ProjectName. I'll then
sum up values on different columns as shown below:

Selection.AutoFilter Field:=1, Criteria1:=AccountID
Selection.AutoFilter Field:=3, Criteria1:=ProjectName
range("A2").select
ACWP=0
Do While Not IsEmpty(ActiveCell)
ActiveCell.offset(1, 0).Select
If ActiveCell.RowHeight <> 0 Then
acwp = acwp + ActiveCell.Offset(0,5).Value
endif
Loop

Other than using "If ActiveCell.RowHeight <> 0 Then" statement to go one row
at a time (including this hidden rows as result of autofilter). which is
quite time consuming.

Questin: Is there a faster method that will allow me just to look at rows
show up as result of AutoFiler ?

Thanks in advance for any suggestion.

 

[Norman Jones]

Hi Wellie,

Try something like:

Sub Tester02()
Dim sh As Worksheet
Dim MyTotal As Double
Dim rng As Range

Set sh = ActiveSheet '<<========CHANGE
MyTotal = 0

With sh.Range("A1")
.AutoFilter Field:=1, Criteria1:=AccountID
.AutoFilter Field:=3, Criteria1:=ProjectName
End With

On Error Resume Next
Set rng = sh.AutoFilter.Range.Columns(1) _
.SpecialCells(xlVisible)
On Error GoTo 0

MyTotal = Application.Sum(rng)

MsgBox MyTotal '<<====== For testing purposes only - DELETE

End Sub

 

[Don Guillett]

try
Sub whatever()
For Each C In Selection.SpecialCells(xlVisible)
If C.RowHeight <> 0 Then acwp = acwp + C.Offset(0, 5)
Next
MsgBox acwp
End Sub

OR leave out the rowheight to see if you get the same
Sub whatever()
For Each C In Selection.SpecialCells(xlVisible)
acwp = acwp + C.Offset(0, 5)
Next
MsgBox acwp
End Sub

 

[Norman Jones]

Hi Wellie,

Change:

MyTotal = Application.Sum(rng)

to

MyTotal = Application.Sum(rng.Offset(0, 5))

 

[Guest]

Thanks Don,

I tried your method, it moves to next cell on the same row instead of nex
visible row.

Can you plz tell me what in struction is required to move to next visible
row ?

Thanks again in advance.

 

[Don Guillett]

I just re-tested and both versions worked fine. How did you modify?? Post
your code.