Programming Questions? Ask a Programmer for Answers ASAP
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 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)