Anthony's VBA Forum

[javaftper]

Hello
I have a large worksheet which is autofiltered. If for example someone selects purchasing manager at the employment column then the spreadsheet will obviously only display purchasing managers. I have added the required VBA to determine what autofiltered criteria has been set which works -- this will be used later to determine when to hide columns -- i.e. if it is a purchasing manager I will hide the telephone number column.
After I had determine the criteria I call a blank row count function which in the purchasing manager scenario should return 13 however it is currently returning 30 -- it seems to be returning all of my rows rather than just the ones that the autofiltered has remained. I am using the cells() referencing method in order to iterate through the remaining rows on the work sheet.
Can anyone assist in advising why I am returning 30 rather than 13?
With the ultimate aim as I said earlier it is to remove various columns which are redundant for various employment types which will be determined using the criteria set and the number of rows that are remaining.
Any help greatly appreciated.
Thanks j

I have pasted my code below and have attached the dataset I'm using for testing.im using 2007 however i saved the dataset as 1997-2003.

[code]
Function AutoFilter_Criteria( Header As Range) As String 'On Error GoTo em Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent. AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 Call rowcountnoblanks End Function Function rowcountnoblanks() 'Application. ScreenUpdating = False 'Range("A3").Select Range("A3").Activate Dim x As Object Dim r As Integer, c As Integer Set x = ActiveCell r = x.Row c = x.Column r = 3 c = 1 Dim i As Integer Dim totalrows As Integer Dim flag As Boolean flag = False i = 1 Do Until flag = True If Cells(r + i, c).Value = "" Then flag = True totalrows = i Else i = i + 1 End If Loop 'Application.ScreenUpdating = True MsgBox totalrows ' result for purchasing manager is 30 however it actually should be 13 -- it appears to be counting the results that are not present when the filters applied End Function

[/code]

ID Company First Name Last Name E-mail Address Business Phone Job Title 01:18:05
1 Company A Anna Bedecs (123)555-0100 Owner last action
2 Company B Antonio Gratacos Solsona (123)555-0100 Owner
3 Company C Thomas Axen (123)555-0100 Purchasing Representative
4 Company D Christina Lee (123)555-0100 Purchasing Manager
5 Company E Martin O’Donnell (123)555-0100 Owner
6 Company F Francisco Pérez-Olaeta (123)555-0100 Purchasing Manager
7 Company G Ming-Yang Xie (123)555-0100 Owner
8 Company H Elizabeth Andersen (123)555-0100 Purchasing Representative
9 Company I Sven Mortensen (123)555-0100 Purchasing Manager
10 Company J Roland Wacker (123)555-0100 Purchasing Manager
11 Company K Peter Krschne (123)555-0100 Purchasing Manager
12 Company L John Edwards (123)555-0100 Purchasing Manager
13 Company M Andre Ludick (123)555-0100 Purchasing Representative
14 Company N Carlos Grilo (123)555-0100 Purchasing Representative
15 Company O Helena Kupkova (123)555-0100 Purchasing Manager
16 Company P Daniel Goldschmidt (123)555-0100 Purchasing Representative
17 Company Q Jean Philippe Bagel (123)555-0100 Owner
18 Company R Catherine Autier Miconi (123)555-0100 Purchasing Representative
19 Company S Alexander Eggerer (123)555-0100 Accounting Assistant
20 Company T George Li (123)555-0100 Purchasing Manager
21 Company U Bernard Tham (123)555-0100 Accounting Manager
22 Company V Luciana Ramos (123)555-0100 Purchasing Assistant
23 Company W Michael Entin (123)555-0100 Purchasing Manager
24 Company X Jonas Hasselberg (123)555-0100 Owner
25 Company Y John Rodman (123)555-0100 Purchasing Manager
26 Company Z Run Liu (123)555-0100 Accounting Assistant
27 Company AA Karen Toh (123)555-0100 Purchasing Manager
28 Company BB Amritansh Raghav (123)555-0100 Purchasing Manager
29 Company CC Soo Jung Lee (123)555-0100 Purchasing Manager
29

[mohan.ankur]

How many rows of data are you trying to filter?
As far as I know Excel filter has a limit 1000 something
_________________
http://www.exceldepot.com
http://www.xlmacros.com
http://www.learnexcel.org