In a current application I have a sheet with multiple tables (NOT Excel structured type - just basic matrices) down the page - and need to use Autolfilters on different tables from time to time (Using XL2007-2010 can only have one set of Autolfilters at a time). In addition, as part of a report printing routine, I need to change the table description to more appropriately describe the report containing the filtered result (e.g. "Obsolete Stock" as a subset of all stocks).
I have managed to achieve this by using a formula that compares the number of spreadsheet rows in the table with the number of text values counted in the filtered range referencing the same spreadsheet rows, viz;
=IF( ROWS(REF1:REF2)<>SUBTOTAL(3,REF1:REF2), "Filtered list description", Table description")
However, some of the tables don't have a range that has all text values [so SUBTOTAL(3,REF1:REF2) won't work], and the numeric columns may contain blanks! So, I got to thinking that a simple UDF could be created to determine if the target range was filtered or not, and drafted the following code:
Public Function FilterOn(Rng As Range) As Boolean
Dim RngVis As Range
Dim c1 As Long
Dim c2 As Long
Application.Volatile
c1 = Rng.Cells.Count
Set RngVis = Rng.SpecialCells(xlCellTypeVisible)
c2 = RngVis.Cells.Count
If c1 = c2 Then
FilterOn = False
Else
FilterOn = True
End If
End Function
My formula would then become =IF( FilterOn(REF1:REF2), "Filtered list description", "Table description").
To my dismay, the code doesn't work, with the same result being returned whether the range is filtered or not (i.e. c2 = RngVis.Cells.Count returns the same number as c1 = Rng.Cells.Count even though some of the cells in the range are filtered out and therefore not visible). This would imply that the code lines shown in red are not doing what I expect them to do.
Anyone got any ideas as to why this is not working, and what modifications are required to rectify the situation?
Cheers
Col
Not 100% following your issue (sometimes its hard to explain just in words I know). But one thing I can tell you is that your code "Rng.Cells.Count" will always give you the same results no matter if there is no records in the cells, all cells filled in, filtered or not filtered. That result is 1048576, which of course is the maximum number of rows in excel. You can test this by simply adding MsgBox c1 & MsgBox c2 to your code. You'll see that this is the answer.
I'm hoping that this can help you. My assumption: you are trying to determine the number of rows in each table & you are not having any issues with your code filtering out the desired results. Change c1 to:
c1 = Application.CountA(Range("A:A")) - of course column A could be changed to any column you want to count.
Change c2 to:
c2 = Range("A" & Rows.Count).End(xlUp).Row - again, change A to what ever column you want to count.
Again, if you add the MsgBox for c1 & c2, you will see that you get different results.
Hopefully that works!
I have used this function in the past
'---------------------------------------------------------------------------------------
' Procedure : TestFiltered
' DateTime : 10/10/2005 18:50
' Author : royUK
' website :
' Purpose : Check if data is Filtered
'---------------------------------------------------------------------------------------
'
Function TestFiltered() As Boolean
Dim rngFilter As Range
Dim r As Long, f As Long
Set rngFilter = ActiveSheet.AutoFilter.Range
r = rngFilter.Rows.Count
f = rngFilter.SpecialCells(xlCellTypeVisible).Count
If r > f Then TestFiltered = True
End Function
Sub TestFunction()
MsgBox TestFiltered
End Sub
in your formula: =IF( ROWS(REF1:REF2)<>SUBTOTAL(3,REF1:REF2), "Filtered list description", "Table description")
I don't get the red part... why use "ROWS" function? The "COUNTA" function should work (I tested in Excel 2010)
=IF( COUNTA(REF1:REF2)<>SUBTOTAL(3,REF1:REF2), "Filtered list description", "Table description")
if you have any hidden rows (not by filter), you can always use 103 instead of 3 in the "SUBTOTAL" function.
I think the FilterMode property would give you the info you need...
Dim x As Boolean
x = ActiveSheet.FilterMode
MsgBox x
This would return True if any filters were applied in a worksheet.
dangelor said
I think the FilterMode property would give you the info you need...Dim x As Boolean
x = ActiveSheet.FilterMode
MsgBox xThis would return True if any filters were applied in a worksheet.
For your info, this nice code works in 2016 if the cursor would be placed within the table.
Thanks for the responses, to which I advise as follows:
James: The variable Rng in the UDF is a user specified range (e.g. "A1:A10") rather than the whole sheet. The purpose of "c1 = Rng.Cells.Count" is to return the actual number of rows in the specified range (without any hidden or filtered rows) to provide the benchmark against which the number of VISIBLE rows in a filtered list will be assessed to determine if a filter has been applied to that range.
Adder: Same answer as for James above (i.e. in my formula based work-around, ROWS(REF1:REF2) is used to return the actual number of rows in the specified range (without any hidden or filtered rows) to provide the benchmark against which the number of VISIBLE rows in a filtered list will be assessed to determine if a filter has been applied to that range.
Dangelor: x = ActiveSheet.FilterMode will only tell me that a filter has been applied somewhere on the target worksheet, which may not be to my target range. As I have a number of different tables/ranges that could have an autofilter applied at different times, I need to know whether or not the target range has autofilters applied and is filtered.
Roy: You're in the same territory, but I get the same (incorrect) result.
Note that "r = rngFilter.Rows.Count" counts the number of rows of the range to which the autolfilter is applied (the benchmark) whereas "f = rngFilter.SpecialCells(xlCellTypeVisible).Count" should return the number of visible cells - which for a multi-column filter range obviously doesn't work. Nevertheless, it doesn't seem to work in a single-column filter range either!
Try it on a single column range, say of 10 rows total including header, with all cells containing unique values. Then filter out two values. r correctly returns 10 but f also returns 10 (incorrectly!) instead of 8. Go figure? It seems to me that the problem lies with rngFilter.SpecialCells(xlCellTypeVisible).Count not correctly returning the count of VISIBLE cells only!
Hi Colin,
Roy was very close, that is the correct approach, the only error was to compare rows with cells, we always should compare the same type of ranges.
The best way to achieve your desired result is to count the cells (or the rows) from the first column of the AutoFilter range, this way it will work with any dimension of the filtered range, it may have 1 column, or 100 columns, we check only the first.
Dim rngFilter As Range, r As Long, f As Long
Set rngFilter = ActiveSheet.AutoFilter.Range
r = rngFilter.Columns(1).Cells.Count
f = rngFilter.Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count
TestFiltered = (r > f)
End Function
Or, by counting rows from the first column, it does not matter, it's the same thing:
Dim rngFilter As Range, r As Long, f As Long
Set rngFilter = ActiveSheet.AutoFilter.Range
r = rngFilter.Columns(1).Rows.Count
f = rngFilter.Columns(1).Rows.SpecialCells(xlCellTypeVisible).Count
TestFiltered = (r > f)
End Function
If you test only the first column, you can even compare cells.count with rows count, the result will be the same ( in the first column, each row will always have only 1 cell):
r = rngFilter.Columns(1).Rows.Count
f = rngFilter.Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count
Catalin
Hi Catalin
I understand what both you and Roy are saying - but it is NOT working in my application!
I tested this code (using rows, cells, and a combination thereof as the object) on a simple 32 row x 3 column range. Column 1 has text values in every cell.
With Autofilter on but no filter criteria applied (i.e. ActiveSheet.FilterMode = False): r = 32 (correct), f = 32 (correct)
With Autofilter on & filter criteria applied to exclude several items (i.e. ActiveSheet.FilterMode = False): r = 32 (correct), f = 32 (incorrect!)
As I said before, it seems to me that the problem lies with rngFilter.SpecialCells(xlCellTypeVisible).Count not correctly returning the count of VISIBLE cells only!
This may not be helpful, but rngFilter.Columns(1).Rows.SpecialCells(xlCellTypeVisible).Address returns exactly the same address as the Autofiltered range, whereas I was expecting it to return an array of individual cell addresses representing the visible cells.
Can you provide more details? What version of excel are you using?
Also, a sample file will say more than any words, please upload a sample file. (always prepare sample files with new topics, it's the best way to make yourself understood and you will receive more accurate answers)
Cheers,
Catalin
Sample file attached - created in Excel 2007.
Set one or both of the filters to see the results
Hi Colin,
The problem is related to what an UDF can and cannot do, sorry I did not see that from the beginning, I was aware of this UDF limitation.
By default, a User Defined Function cannot make changes to any cell, it will affect the calculation tree. More details here: Excel Recalculation
A worksheet function that changes the structure of the worksheet by, for example, copy -pasting a range of cells will fail. Excel will simply ignore the code lines that are attampting to change cells, so the reason for the failure is not obvious.
The Autofilter method is in the black list, as it will make changes to the values of the cells, it will simply not work.
If you try this code, you will see that the code works in a normal procedure, but not in a UDF:
Dim rngFilter As Range, r As Long, f As Long
Application.Volatile
Set rngFilter = ActiveSheet.AutoFilter.Range
r = rngFilter.Columns(1).Rows.Count
f = rngFilter.Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count
MsgBox (r > f)
End Sub
For a UDF, we have to use a different approach, by evaluating other cell properties, like RowHeight, or EntireRow.Hidden, like in the example below:
Test2 = False
Dim cell As Range
For Each cell In rng
If cell.EntireRow.Hidden Then Test2 = True: Exit For
Next
End Function
For large ranges, the code will exit at the first hidden row, there is no need to evaluate all remaining cells.
You can also use worksheet functions in VBA, to calculate a count of visible rows:
Or:
Let us know if this solves your problem.
Catalin
Catalin: Thanks for the explanation - an interesting/intriguing snippet that is not readily apparent (I've never seen anything written about limitations of UDFs!)
I will now review my code to see how I can adapt it to accommodate this limitation - though I want to make it a generic function that can be used on any range.
Cheers
Col
Catalin: Your function using the For Next Loop, viz;
Test2 = False
Dim cell As Range
For Each cell In rng
If cell.EntireRow.Hidden Then Test2 = True: Exit For
Next
End Function
seems to work, and is the simplest, so I'll use that code.
I was fixated on using SpecialCells(xlCellTypeVisible) as I understood it was specifically designed for doing what I wanted (which I guess it does - it's just the limitation of using Autofilter in a UDF that's the real problem.)
Many thanks
Great, glad to hear that your problem is solved.
Just make sure that you don't pass entire columns to the function, if there is no filter applied, the function will loop through all cells from the range indicated.
Also, you should check only the first column from the range, otherwise the function will loop through each cell, even if they are in the same row. Cells from the same row will always have the same height. Passing a range like Sheet1!A:C will start a loop through over 3 milion cells... If you use the function in 10 cells, this means 30 milion cells to be evaluated 🙂
There is an exit in the function, but if no rows are hidden, the function will not stop until it will evaluate all cells.
Test2 = False
Dim cell As Range
For Each cell In rng.Columns(1)
If cell.EntireRow.Hidden Then Test2 = True: Exit Function
Next
End Function
