How To Count The Number Of Records Selected By AutoFilter

[jershie]

I have set up a number of macros in an excel spreadsheet to essentially go into the AutoFilter column headings and filter based on certain requirements. I have a number of users that then run these macros and report their counts for the day (at the beginning and end). Essentially, I would like to remove the person from the reporting process, and find a way to count the number of records (in a separate sheet) resulting from each macro as it is activated. Any ideas?

[Bob Phillips]

Use SpecialCell(xlCellTypeVisible) on the range being filtered.

[jershie]

Any links that will help me set up this code? I'm relatively new to VB . . . Tried googling your response, but got some threads that were way over what I am trying to do & had conflicting statements from the various experts regarding the setup of the code . . .

The data I want to count is in worksheet "ROP Spread" & as each macro is run, I want to take the count (along with some type of identifying information in case the user activates the macro out of the intended sequence) and paste it into a worksheet entitled "Stats". My current macro coding is attached.

--------------------------------------------

Sub Insufficients_Part1()
'
' Insufficients_Part1 Macro
'
' Keyboard Shortcut: Ctrl+W
'
On Error Resume Next
Sheets("ROP Spread").Select
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=13, Criteria1:="<0", Operator:=xlAnd
Range("A1").Select
End Sub

[RonMcK]

Jershie,

Your first msg spoke of each his reporting their counts at the beginning and end of each day. Is the difference for a day for a user equal to the total of lines found by each macro the user runs?

You probably want to capture: date, time, computerID, user, name of macro, and number of lines, at least. Is there any other information that will help you with your audit trail?

Thanks,

[jershie]

All the info you mentioned would be great and it is more than I could've hoped for. At the least I would like names of macro and # of lines. But all the others would make my life much easier and would avoid confusion and more headache down the road!

[Bob Phillips]

Here is a starter



Sub Insufficients_Part1()
Dim rng As Range

On Error Resume Next

With Worksheets("ROP Spread")

.ShowAllData
Set rng = Range("A1:Z26")
rng.AutoFilter Field:=13, Criteria1:="<0"
Set rng = rng.SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then

rng.Copy
'paste somewhere
End If
End With
End Sub

[jershie]

XLD, made a few changes and it worked splendidly! Thanks!

RonMcK, still curious about how to get IP addies, etc as part of my macro though . . . Would love to hear back from you!

[RonMcK]

Jershie,

Tell me more about what you want to capture to meet you needs. In many ways, I'm a learner like yourself, vis a vis getting Excel to do useful things. A resource I bring to the table is 15+ years of programming business systems.

In your posts you spoke of your users needing to tell you daily their beginning and ending counts of lines isolated through their use of one or another of your macros. How do you use this information? Why ask them for their beginning number of lines (sum of prior days' lines?)?

How many users do you want to track data for? Are they all working with one data file or are there multiple data files?

Thanks,