# Solved: Excel 07 VBA - Counting all Visible rows after a filter was applied.



## TMeerkat (Jun 23, 2011)

Hi there

I would like to count the visible rows of a table (tblProbRep) that has been filterred and then to select the last cell in column H to copy to another sheet.
I know the code for all except how to count *only visible *rows in vba .
Please can someone assist.

Thanks you
TMeerkat


----------



## CDHarm (May 26, 2011)

TMeekat,

Here's a bit of code that I use to count the "Visible" rows.


```
Dim Myval as Intiger
Myval = .Range("c2:c" & lrow).SpecialCells(xlCellTypeVisible).Count
```


----------



## TMeerkat (Jun 23, 2011)

Hi there

When I use this code
Dim Myval As Integer
Myval = Sheets("ProblemReporting").Range("tblProbRep").SpecialCells(xlCellTypeVisible).Count

MsgBox "This is " & Myval, vbOKCancel

it gives me 15 when there is only 6 rows and only 3 rows are visible.
Why is that?

Did I do something wrong?


----------



## AD_Taylor (Jan 16, 2010)

Is the range "tblProbRep" 5 columns wide?

If so you because you are counting cells that are visible you have 3 rows by 5 columns = 15 cells visible?

You could try:

```
Myval = Sheets("ProblemReporting").Range("tblProbRep").[B]Columns(1).[/B]SpecialCells(xlCellTypeVisible).Count
```
 This should narrow the range down to just the first column so that it will only count the rows.

Hope this helps!
Adam


----------



## CDHarm (May 26, 2011)

Adam,

Thanks for the answer. I learned something from this.


----------

