How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask J. Mar Your Own Question
J. Mar
J. Mar, Consultant
Category: Programming
Satisfied Customers: 375
Experience:  Programmer of COBOL, COBOL II, C++, C#,Java, MS EXCEL VBA, MS ACCESS VBA, Visual Basic .Net
21350990
Type Your Programming Question Here...
J. Mar is online now
A new question is answered every 9 seconds

Counting Visible rows only using vba code I have a table

Customer Question

Counting Visible rows only using vba code:
I have a table (tblProbRep) that gets filterred. i need to count the filterred visible rows only. What would the code look like if I click on a button and it counts only the visible rows and gives me the amount (like 9)
I then need to specify that in column H in the number of rows visible counted is the answer I want placed into a cell.

for instance:
'RowCount - is the amount of visible rows
'....code to count the visible rows.......
Sheets("Sheet1").Range("A1")=Sheets("Sheets2").Range("tblProbRep").Cells(RowCount,"H")

or something to that effect.

Please can someone help.
Kind Regards
Customer
Submitted: 5 years ago.
Category: Programming
Expert:  J. Mar replied 5 years ago.

Thank you for asking your question on JustAnswer. My name is XXXXX XXXXX I will be happy to help you.

 

You can creat a function or paste the following in a stand alone module.

 

Function CountVisibleFilterRows(MyRange As Range) As Long
On Error Resume Next
CountVisibleFilterRows = MyRange.SpecialCells(xlCellTypeVisible).Count
End Function

 

Then you can access the function with this, for example;

 

CountVisibleFilterRows(Range("A1:A20")) in Excel formula or

 

in VBA use;

 

Application.WorksheetFunction.CountVisibleFilterRows(Range("A1:A20"))

Customer: replied 5 years ago.

Hi there

Do I just use it as is?

Function CountVisibleFilterRows(MyRange As Range) As Long
On Error Resume Next
CountVisibleFilterRows = MyRange.SpecialCells(xlCellTypeVisible).Count
End Function

Application.WorksheetFunction.CountVisibleFilterRows(Range("A1:A20"))

or do i substitute my information in there and what goes where?

When I use the information in my file, it gives me 0 not 3 or something no matter what is visible.

Expert:  J. Mar replied 5 years ago.

Sorry, the function I mentioned before should be re-written as follows;

 

Substitute your information where necessary

 

Function CountVisibleFilterRows(ByVal Sh As Worksheet)
Dim Target As Range
Dim c As Range
Dim i As Long
'If the Filter is not used
If Sh.FilterMode = False Then
CountVisibleFilterRows = 0
Exit Function
End If
Set Target = Sh.AutoFilter.Range
For Each c In Target.SpecialCells(xlCellTypeVisible).Areas
i = i + c.Rows.Count
Next
CountVisibleFilterRows = i - 1 '-1 stands for remove header row
End Function

Expert:  J. Mar replied 5 years ago.

Here is an example how to use in VBA

 

Application.WorksheetFunction.FilteredRowsCount(ActiveSheet) ; where "ActiveSheet" is the Worksheet your filter is on

Customer: replied 5 years ago.

Thanks for trying to help

 

I actually got the code from another website and changed it a little

it goes like this:

 

Dim rng As Range

Dim RowCount as Integer, r as Integer


Set rng = Sheets("ProblemReporting").AutoFilter.Range
RowCount = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
'MsgBox RowCount
r = RowCount
txtProjProgUpd.Value = Sheets("ProblemReporting").Range("tblProbRep").Cells(r, 2)

Expert:  J. Mar replied 5 years ago.
Thank you

Related Programming Questions