Hi there you all!

i've the following problem. I've a database with various fields where
some alphanumeric product codes can be repeated several times. The user
can work on it using Excel filters to mainpulate the values. I use the
SUBTOTAL function on some of these fields to get the filtered sum of
them, but I need to know how many unique codes are shown in the
filtered list (no matter how the filter has been set from the user).

To solve the problem I've built a user defined function that runs
through a given range, builds up an array of unique values and makes a
count of them, ignoring hidden rows (to keep just the filtered,
visible, ones).

The function works ok, except when you delete a row from the sheet!
Through debugging, I've discovered that Excel has no idea if a row is
Hidden or not, when you delete a (different) row! The property Hidden
is unavailable for the whole Sheet and the function ends up with a
runtime error!

Is this a Microsoft bug? Is there a way to avoid this from happening?

The function goes like this:



Function CountSKU(rng As Range)

Dim c As Range, lista() As String, L As Variant
Dim i As Integer, cVal As Variant

ReDim lista(0)

On Error GoTo esci

Application.EnableEvents = False

For Each c In rng.Cells
If c.Rows(1).EntireRow.Hidden = False Then
If c.Value < Empty Then
cVal = c.Value
For Each L In lista
If StrComp(cVal, L, vbTextCompare) = 0 Then Exit
For
Next L
If L = Empty Then
'If UBound(Filter(lista, cVal, True, vbTextCompare)) =
-1 Then
If lista(0) = Empty Then
lista(0) = CStr(cVal)
Else
i = UBound(lista) + 1
ReDim Preserve lista(i)
lista(i) = CStr(cVal)
End If
End If
End If
End If
Next c

esci:

Application.EnableEvents = True
If Err = 0 Then
If UBound(lista) 0 Or lista(0) < Empty Then
ContaSKU = UBound(lista) + 1
Else
ContaSKU = 0
End If
Else
ContaSKU = CVErr(xlErrNA)
End If

End Function




Please note that the method Range.SpecialCells gives a run-time error
as well, in the above circumstances, so it's useless!


BTW, does anybody know if there's a bug in the CurrentRegion method in
Excel 2000?
Here at home with Excel 2003 it works perfectly, but at my office I've
Excel 2000 and avery time the code tells VBA to get a
Range.CurrentRegion the Selection_Change event of the Worksheet
(Range.Parent) starts running! I had to put a load of
Application.EnableEvents=False all around the code! :O

I forgot to mention that the function bug comes out only when you
insert or delete a row through VBA code, _not_ through normal Excel
user interface!!! Just try to record a macro where you delete a row
(everything seems to be ok) and then play it (and the error comes)!

Hi Upanisad,

Why not pass the the visible cells to a collection (or a dictionary object)
and interrogate the collection's count property,

---
Regards,
Norman



"Upanisad" wrote in message
ups.com...
I forgot to mention that the function bug comes out only when you
insert or delete a row through VBA code, _not_ through normal Excel
user interface!!! Just try to record a macro where you delete a row
(everything seems to be ok) and then play it (and the error comes)!

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!

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!

Oh, I didn't know the Application.Volatile mehod! I just put this line
at the begining of my function and it works even when deleteing rows by
VBA code!

On Error Resume Next
If rng.Rows(1).Hidden < rng.Rows(1).Hidden Then Application.Volatile
(True)

Thank you!