What is the better way to scan cells after autofiler

[Wellie]

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


---
Regards,
Norman



"Wellie" <Wellie@discussions.microsoft.com> wrote in message
news:E076407D-7251-4159-853A-AC9862063E7A@microsoft.com...
>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,

Change:

> MyTotal = Application.Sum(rng)

to

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


---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:ePU9LqF9EHA.2600@TK2MSFTNGP09.phx.gbl...
> 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
>

[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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Wellie" <Wellie@discussions.microsoft.com> wrote in message
news:E076407D-7251-4159-853A-AC9862063E7A@microsoft.com...
> 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.
>

[Wellie]

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
> donaldb@281.com
> "Wellie" <Wellie@discussions.microsoft.com> wrote in message
> news:E076407D-7251-4159-853A-AC9862063E7A@microsoft.com...
> > 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.
> >
>
>
>

[Don Guillett]

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Wellie" <Wellie@discussions.microsoft.com> wrote in message
news:2B0D43AD-E138-45BF-BF92-14763C7A76C5@microsoft.com...
> 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
> > donaldb@281.com
> > "Wellie" <Wellie@discussions.microsoft.com> wrote in message
> > news:E076407D-7251-4159-853A-AC9862063E7A@microsoft.com...
> > > 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.
> > >
> >
> >
> >

[Wellie]

Hi Don

Thanks for responding again. The following is my actual code as shown below. Instead of using a variable, I inserted a sample of critera in a test routine. The code appears not able to move to next row.

Please help and let me know what is wrong in the code below.


Private Sub CommandButton2_Click()
Dim value As Variant

If ActiveSheet.AutoFilterMode = True Then
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Else
Range("A1").Select ' Enable auto filter on this row
Selection.AutoFilter
End If

value = 0
Selection.AutoFilter Field:=4, Criteria1:="Cost Performance Index (CPI)"
Selection.AutoFilter Field:=5, Criteria1:="Budgeted Cost of Work Scheduled (BCWS) ($K)"

For Each C In Selection.SpecialCells(xlVisible)
Debug.Print "Cell("; C.Row; ","; C.Column; ")=";
If C.Row <> 1 Then ' Skip heading
Debug.Print C.Offset(0, 5); " or Value="; value
value = value + C.Offset(0, 5)
End If
Next

Here is the debug.print output
Cell( 5 , 54 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 55 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 56 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 57 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 58 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 59 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 60 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 61 )= or Value=13/04/2004 9:59:30 AM
Cell( 5 , 62 )= or Value=13/04/2004 9:59:30 AM

[Wellie]

Here is the actual spreadsheet as attached