Hi Upanisad,

Anyway that doesn't solve the problem, which is the fact that Excel
loses any Hidden (and most of the others too!) property of a cell, if
you use a user defined function and delete a row using VBA!

The following function worked for me when used as worksheet function, or in
VBA:

'==========================
Public Function FilterUniqueCount()
Dim Rng1 As Range, rng2 As Range
Dim rCell As Range
Dim Col As Collection

Application.Volatile

If Not ActiveSheet.AutoFilterMode Then
FilterUniqueCount = 0
Exit Function
End If

Set Rng1 = ActiveSheet.AutoFilter.Range
Set rng2 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1)


Set Col = New Collection

Application.Volatile (True)
For Each rCell In rng2.Cells
If Not IsEmpty(rCell.Value) Then
If Not rCell.EntireRow.Hidden = True Then
On Error Resume Next
Col.Add rCell.Value, CStr(rCell.Value)
On Error GoTo 0
End If
End If
Next rCell
On Error Resume Next

FilterUniqueCount = Col.Count

End Function
'<<==========================

I tested it with a multi-column autofilter and experienced no problem,
either before. or after, deleting rows. This was true for manually deleted
rows and similarly true for programmatically deleted rows

To delete rows via VBA, I used the simple:

'==========================
Sub TestDelRows()
Selection.EntireRow.Delete
ActiveSheet.Calculate
End Sub

'<<==========================

Beacuse a collection/dictionary is much slower then an array! I've read
that adding an element to a collection requires about 10 (or was 100?)
times the er..time.. required to insert a value in an (pre-dimensioned)
array. Even using ReDim Preserve is faster then an Add, from some tests
I've made!

I did not look at your attempt, but since your approach did not work for
you, I went my own way. I did not do any speed test beyond perceived
response time, as my primary concerns were to produce a working function and
to test your assertions.

Finally, as an alternative for use in VBA, I used the SpecialCells method:

'==========================

Public Function FilterUniqueCount2() As Long

Dim Rng1 As Range, rng2 As Range, rng3 As Range
Dim rCell As Range
Dim Col As Collection

Set Rng1 = ActiveSheet.AutoFilter.Range
Set rng2 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1)
Set rng3 = rng2.SpecialCells(xlCellTypeVisible)

If Not ActiveSheet.AutoFilterMode Then
FilterUniqueCount = 0
Exit Function
End If

Set Col = New Collection

For Each rCell In rng3.Cells
If Not IsEmpty(rCell.Value) Then
On Error Resume Next
Col.Add rCell.Value, CStr(rCell.Value)
On Error GoTo 0
End If
Next rCell
On Error Resume Next

FilterUniqueCount2 = Col.Count

End Function
'<<==========================

The latter function is unsuitable for use as a UDF because of known problems
related to the use of the SpecialCells method in this context.


---
Regards,
Norman



"Upanisad" wrote in message
oups.com...
Beacuse a collection/dictionary is much slower then an array! I've read
that adding an element to a collection requires about 10 (or was 100?)
times the er..time.. required to insert a value in an (pre-dimensioned)
array. Even using ReDim Preserve is faster then an Add, from some tests
I've made!

Anyway that doesn't solve the problem, which is the fact that Excel
loses any Hidden (and most of the others too!) property of a cell, if
you use a user defined function and delete a row using VBA!

I can't build up a collection either, because the For..Next loop gives
a runtime error when I check if the row is hidden and SpecialCells
doesn't work either. It thinks that every cell is visible (when it's
not if you look at the Sheet while in debug mode)! It's not a code
problem: I've tried setting up a watch point in the VBA IDE... most of
the properties of every cell of the sheet are lost! They reappear when
Excel ends calculating all the cells!