unique count of filtered data

[Danny54]

im having problems with the following vba code in my macro. If my filtered data has more than 1 row then the .Count is valid. If its only 1 or none found, the macro goes into a loop or at least it appears to be a look. I let it run for over a hour and it still was running when i stopped the macro. I've seen something about coding like this is bad.

Any suggestions?

what the code does is filter column 7 for the value Money. With the CreateObject piece, it counts only the unique values in Column A. Then I write the count to Sheet1.



ws.Range("A1:H999999").Select
Selection.AutoFilter
wb.ActiveSheet.Range("$A$1:$H999999").AutoFilter Field:=7, Criteria1:= _
"*Money*", Operator:=xlAnd

With CreateObject("scripting.dictionary")
For Each Cl In wb.ActiveSheet.Range("A2", wb.ActiveSheet.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
Debug.Print Range("A2", wb.ActiveSheet.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Address

If (Cl <> "Name") Then
.Item(Cl.Value) = Empty
End If
Next Cl
'MsgBox .Count
Worksheets("Sheet1").Cells(7, 12) = .Count
End With

wb.ActiveSheet.ShowAllData

 

[jasonb75]

I'm assuming that there is more to your code, what you have shown us would not get stuck in a loop, however it would generate a runtime error. The fact that it doesn't implies that you have an error trap somewhere which could be causing an endless loop.

Please remember to use code tags with your code (click the </> icon, then paste the code into the popup window).

 

[Danny54]

printing the
wb.ActiveSheet.Range("A2", wb.ActiveSheet.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) normally shows a good range if two or more rows of data are found after filtering - 3 counted if header included.

Filtered data
Header
row1
row2

the range returned is $A$2:$A$3

If I have only 1 row and the header
FIltered data
Header
row1

the range returned is $1:$2,$38:$1048576

now when this code is entered it just loops and loops

If (Cl <> "Name") Then
.Item(Cl.Value) = Empty
End If
Next Cl


so, is my range statement incorrectly written?

Suggestions

 

[jasonb75]

Try changing it to

wb.ActiveSheet.Range("A2:A"& wb.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

The part wb.ActiveSheet is something that I'm not sure of, if wb refers to an inactive workbook then I don't see how the sheet can be active, although it appears that it is working, if it wasn't I would have expected a runtime error. That is assuming that you don't have On Error Resume Next with inadequate handling of individual errors.

 

[Danny54]

same loop problem

my debug window
? wb.ActiveSheet.Range("A2:A"& wb.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).address
$1:$2,$38:$1048576

is it something where the range actually overlaying the header. when I have two or more records it always works fine. Its only when I have a header and 1 row of data.

funny thing, it works find if there is no data as well but that probably because its not looking for dupes because the header row contains the value of Name

If (Cl <> "Name") Then

 

[jasonb75]

What about

Intersect(wb.ActiveSheet.UsedRange,wb.ActiveSheet.Range("A2:A"& wb.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible))

As noted in my earlier comments, I suspect that the problem is in parts of the code that you haven't posted. Without further clarification or the rest of the code (properly tagged) this is my last guess.

 

[Danny54]

that returned

? Intersect(wb.ActiveSheet.UsedRange,wb.ActiveSheet.Range("A2:A"& wb.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)).Address
$A$1:$L$2

I would espect the value im looking for would be $A$1:$A1$A3

 

[jasonb75]

It works fine for me, your problem is either merged cells or a different part of the code.

 

[Danny54]

is it because i have blank rows?

 

[Danny54]

ok,
perhaps its easier to ask and start over.

Im trying to filter two columns called color and type

color type
orange airplane
red car
blue car
green bus
blue car
red car

if i filter type for the word car the result set is all the rows having car

color type
red car
blue car
blue car
red car

then i want to get of count of unique color from this filtered result set which would be

unique_count = 2


I've coded so much vba its starting to run together and I know it shouldnt be this hard. Im trying to make it difficult. Any better solutions for using a filter, find count of unique value looking at a different row (one thats not being filtered?)

Thanks