SpecialCells(xlCellTypeVisible).Count giving different results

[netrixuser]

Hello all, I was looking for a way to filter a column and then count the number of entries in that column and assign that number to a variable. I'm assuming the code I have written is incorrect but the weird thing is that I get the correct result when I filter on one column and use my count "formula" but when I remove that filter an use the same formula (different variable assigned) for another filtered column, I get the wrong number returned for the number of visible cells.

In the VBA code pasted below - it is the count for column P that returns the wrong value whereas the preceding count for column N is correct:

This returns 72 which i the number of cells in column N when filtered
GTVS = Range("N1:N" & lastRow).SpecialCells(xlCellTypeVisible).Count - 1

Whereas this returns 404 which is the value of the variable lastRow minus 1 and not the number of cells in column P when filtered
MOBL = Range("P1:P" & lastRow).SpecialCells(xlCellTypeVisible).Count - 1

I tried just selecting column P (ie removing & lastRow) but that counts all cells in the column

Thanks in advance for any/all guidance !
Regards

Netrix

Option Explicit
Dim lastRow As Integer
'Dim K As Integer
Dim GTVS As Integer
Dim MOBL As Integer
Dim SMS As Integer
Dim Whats As Integer
Dim WeCh As Integer

Sub filterCount_GTVS()
Dim sh As Variant
    'Check if sheet exists  - delete if yes
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name = "Statistics" Then
            Application.DisplayAlerts = False
            sh.Delete
            Application.DisplayAlerts = True
        End If
    Next sh

    Worksheets("MRL_Report").ShowAllData
    'find last row of the worksheet - for this example lastRow = 405
    lastRow = Sheets("MRL_Report").Range("N" & Rows.Count).End(xlUp).Row


    'Filter column A for certain users
    Sheets("MRL_Report").Cells.CurrentRegion.AutoFilter Field:=1, Criteria1:=Array( _
    "BPIM", "GPTI", "RPTE"), Operator:=xlFilterValues
 
    'Filter column N for colour pink
    Sheets("MRL_Report").Range("$N$1" & lastRow).AutoFilter Field:=14, Criteria1:=RGB(255, _
    199, 206), Operator:=xlFilterCellColor
    
    'Count visible cells in column N - start at header row and then delete "1" from total to get around filter returning no cells
    GTVS = Range("N1:N" & lastRow).SpecialCells(xlCellTypeVisible).Count - 1

'THE LINE ABOVE WORKS CORRECTLY AND RETURNS 72 FOR THE VARIABLE GTVS
        
     'Add new sheet - add values from count above
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Statistics"
    Sheets("Statistics").Range("B2").Value = "GTVS"
    Sheets("Statistics").Range("B3").Value = GTVS
        
    'Remove filter from GTVS column N (pink) but keep filter on column A
    Sheets("MRL_Report").Cells.CurrentRegion.AutoFilter Field:=14


    'Filter column P for colour pink
    Sheets("MRL_Report").Cells.CurrentRegion.AutoFilter Field:=16, Criteria1:=RGB(255, _
    199, 206), Operator:=xlFilterCellColor
    
    'Count visible cells in column P
    MOBL = Range("P1:P" & lastRow).SpecialCells(xlCellTypeVisible).Count - 1

' ****** THE LINE ABOVE, IDENTICAL TO THE FIRST COUNT EXCEPT THE VARIABLE NAME AND COLUMN, RETURNS 404 - THIS IS THE VALUE FOR lastRow MINUS 1 ***********
     
     'Add values from counts
    Sheets("Statistics").Range("C2").Value = "MOBL"
    Sheets("Statistics").Range("C3").Value = MOBL
        
     'Remove filter from MOBL column N
    Sheets("MRL_Report").Range("$A$1:$A$405").AutoFilter Field:=16

 

[RoryA]

You added a new sheet, which becomes the active sheet, and because you didn't qualify your Range call with a worksheet, it is defaulting to the active sheet where all the cells are visible. You should use:

Sheets("MRL_Report").Range("P1:P" & lastRow).SpecialCells(xlCellTypeVisible).Count - 1

and really you should do that in the first bit of code too.

 

[Marc L]

Hello,​

​

another way is to use the Excel worksheet function SUBTOTAL even under VBA rather than SpecialCells …​

 

[netrixuser]

You added a new sheet, which becomes the active sheet, and because you didn't qualify your Range call with a worksheet, it is defaulting to the active sheet where all the cells are visible. You should use:

Sheets("MRL_Report").Range("P1:P" & lastRow).SpecialCells(xlCellTypeVisible).Count - 1

and really you should do that in the first bit of code too.

D'oh !! thank you RoryA

 

[netrixuser]

Hello,​

​

another way is to use the Excel worksheet function SUBTOTAL even under VBA rather than SpecialCells …​

Thanks for the suggestion - I will plod on with the SpecialCells route for now but will definitely have a look at this

 

[ABennett757]

Hi, first of all, thank you to all who participated in this post previously as this code helped me out. However, I am trying to make one tweak and can't seem to get the syntax correct. Currently my code is working as expected (code is below) and counts all cells that have data entered in the Range O8:O after my table has been filtered (table header is row 7). However, I would like to use a named range instead of O8:O so that if a user were to add columns our rows outside of this range, my code will still be counting the intended column/data. My named range is defined and called "BOM_Column_Model" but I can't figure out the syntax to replace O8:O with the named range in the code below. Any help would be appreciated.

Dim ItemNo As Integer
ItemNo = Application.WorksheetFunction.Subtotal(3, Range("O8:O" & Rows(Rows.Count).End(xlUp).Row))