SpecialCells(xlCellTypeVisible).Count giving different results

netrixuser

New Member
Joined
Jan 21, 2019
Messages
32
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

VBA Code:
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,120
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

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

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

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,775
Office Version
  1. 2010
Platform
  1. Windows
Hello,​
another way is to use the Excel worksheet function SUBTOTAL even under VBA rather than SpecialCells …​
 

netrixuser

New Member
Joined
Jan 21, 2019
Messages
32
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:

Code:
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

New Member
Joined
Jan 21, 2019
Messages
32
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

New Member
Joined
Mar 25, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,564
Messages
5,770,886
Members
425,649
Latest member
cbTexas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top