What is the better way to scan cells after autofiler
 

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.

What is the better way to scan cells after autofiler
 

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


---
Regards,
Norman



"Wellie" wrote in message
...
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.

What is the better way to scan cells after autofiler
 

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

--
Don Guillett
SalesAid Software

"Wellie" wrote in message
...
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.

What is the better way to scan cells after autofiler
 

Hi Wellie,

Change:

MyTotal = Application.Sum(rng)

to

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


---
Regards,
Norman



"Norman Jones" wrote in message
...
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


---
Regards,
Norman

What is the better way to scan cells after autofiler
 

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" wrote:

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

--
Don Guillett
SalesAid Software

"Wellie" wrote in message
...
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.

What is the better way to scan cells after autofiler
 

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

--
Don Guillett
SalesAid Software

"Wellie" wrote in message
...
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" wrote:

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

--
Don Guillett
SalesAid Software

"Wellie" wrote in message
...
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.