Auto-generate count of items in a column - Excel

[xiaonajia]

Column A|B|C|D|E|F|G

Fruit|Date|Countif| (blank) |Fruit|Date|Countif

I1 refers to the date to filter by.

Column C contains the countif formula

Private Sub Duplicate_Click()
On Error Resume Next
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
On Error GoTo 0
ActiveSheet.Range("A1:C9999").Copy
'PasteSpecial Values Only
ActiveSheet.Range("E1:G9999").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard (removes "marching ants" around your original data set)
Application.CutCopyMode = False
ActiveSheet.Range("F:F").NumberFormat = "dd-mmm-yy"

ActiveSheet.Sort.SortFields.Clear

ActiveSheet.Range("E1:G9999").AutoFilter Field:=1, Criteria1:="<>0"
ActiveSheet.Range("E1:G9999").AutoFilter Field:=2, Criteria1:=">=" & Range("I1")
ActiveSheet.Range("E1:G9999").Sort Key1:=Range("G1"), Key2:=Range("F1"), Header:=xlYes, _
    Order1:=xlDescending, Order2:=xlDescending
 
ActiveSheet.Range("E1:G9999").RemoveDuplicates Columns:=1, Header:=xlYes
 
End Sub

Since you asked and to help others, here's the code

 

[davidktw]

Column A|B|C|D|E|F|G

Fruit|Date|Countif| (blank) |Fruit|Date|Countif

I1 refers to the date to filter by.

Column C contains the countif formula

...

ActiveSheet.Range("E1:G9999").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

[/CODE]

Since you asked and to help others, here's the code

Fantastic. Of course when it is help I need, I asked. I only set challenge when I already have the answer.

Here are mine

 

[xiaonajia]

nice, thanks for sharing with the community