Unexpected Results With VBA Formula

[Ark68]

This is a snippit of my module code:

With ws_cd1
            If pbypass = True Then
                cnt_date = Application.WorksheetFunction.CountIf(.Columns(2), n_date)
            Else
                cnt_date = Application.WorksheetFunction.CountIf(.Columns(1), n_date)
            End If
            cnt_rows = Application.WorksheetFunction.Count(.Columns(1))
            rte = cnt_rows - cnt_date
            
            'unprotect core data
            .Unprotect
            With ws_gui1.Range("AP3") 'coredata unlock, fee data is already unlocked
                .Value = "CORE DATA   " & Chr(208) 'unlocked
                .Font.Name = "Arial Narrow"
                .Characters(Len(.Value), 1).Font.Name = "Webdings"
            End With
            
            'filter core data of all dates not equal to queried date
            If pbypass <> True Then
                If .AutoFilterMode Then .AutoFilterMode = False
                .Range("A1").AutoFilter Field:=1, Criteria1:="<>" & n_date
                test = Application.WorksheetFunction.Subtotal(1, .Columns(1))
                'check for data error (count number of filtered rows to difference of queried date from total rows - should be equal)
                If test <> rte Then
                    MsgBox "Data inconsitency encountered: Core_Data Date Redundancy"
                    Stop
                End If

Stepping through the code with pbypass = False,
n_date = 2020-05-23
cnt_date = 24
cnt_rows = 6505 (calculated prior to this snippit)
rte = 6482
Worksheet ws_cd1 is the data source, column 1 holds dates.
The autofilter has has filtered out all instances on 2020-05-23, the visible rows are the other dates.
The problem is with the line in red ... a line of code which I was hoping would given me a count of the number of visible cells. With the 24 rows of data filtered out for 2020-05-23, there should be 6482 visible rows (RTE). I would anticipate that the value of TEST would give me that same value ... the number of rows visible in the data worksheets ws_cd1.
But it's not. It's giving me a value of 44060.9782474545.

Is anyone able to provide a solution to this problem?

 

[JackDanIce]

Try:

test = .columns(1).SpecialCells(xlCellVisible).Count

You're using Subtotal to sum the values of the visible cells not count them, hence your result is mixed number, not whole number.

 

[Ark68]

Hi JackDanice, thank you for your suggestion.
I substituted my line with yours but I am now encountering an error:
"Unable to get the SpecialCells property of the Range Class"

 

[JackDanIce]

Unsure, maybe try:

Msgbox .Parent.Name 'Confirm what this is
test = .Range("A:A").SpecialCells(xlCellVisible).Count

 

[Fluff]

It needs to be either

Range("A:A").SpecialCells(xlCellTypeVisible).Count

or

Range("A:A").SpecialCells(xlVisible).Count

 

[MARK858]

@Ark68, just to also point out that Count is Subtotal(2, Subtotal(1, is Average. The hidden values part in the table only applies to manually hidden rows, if they are hidden by Autofilter then it automatically only includes the visible cells.

Function_num	Function_num	Function
(includes hidden values)	(ignores hidden values)
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

 

[Ark68]

Thank you all for you help. It's very appreciated to see the interest in wishing to help.
Fluff and JackDanIce, your suggestions would have worked for my immediate needs, but the value still wasn't what was to be expected. I was getting a value of 1048552. As mentioned, this would get me through the first test, but I think it would have messed up more when I need to rely on a more accurate value of TEST further on in my project.

Mark858, I substituted based on your explanation :

test = Application.WorksheetFunction.Subtotal(1, .Columns(1))

with ...

test = Application.WorksheetFunction.Subtotal(2, .Columns(1))

With positive results.

 

[MARK858]

Happy we helped