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 RegardsXXXXXXXX
Computer OS: Windows 7
Programming Language: VBA
Thank you for asking your question on JustAnswer. My name is XXXXX XXXXX X 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 LongOn Error Resume NextCountVisibleFilterRows = MyRange.SpecialCells(xlCellTypeVisible).CountEnd Function
Then you can access the function with this, for example;
CountVisibleFilterRows(Range("A1:A20")) in Excel formula or
in VBA use;
Do I just use it as is?
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.
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 RangeDim c As RangeDim i As Long'If the Filter is not usedIf Sh.FilterMode = False ThenCountVisibleFilterRows = 0Exit FunctionEnd IfSet Target = Sh.AutoFilter.RangeFor Each c In Target.SpecialCells(xlCellTypeVisible).Areasi = i + c.Rows.CountNextCountVisibleFilterRows = i - 1 '-1 stands for remove header rowEnd Function
Here is an example how to use in VBA
Application.WorksheetFunction.FilteredRowsCount(ActiveSheet) ; where "ActiveSheet" is the Worksheet your filter is on
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.RangeRowCount = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1'MsgBox RowCountr = RowCounttxtProjProgUpd.Value = Sheets("ProblemReporting").Range("tblProbRep").Cells(r, 2)