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
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