Keep only those rows having documents no. repeat > 4 times

[RENU1973]

I want to understand the VBA query that keeps only those rows which 'DocumentNo' having more than 4 and delete all the rows having less than 4 or equal to 4.
Refer attach file,'DocumentNo'have in column B and take an example of 'Document no'9301045130 ,which is repeating 5 times and it should be keep.

[Rudi]

This macro will do what you need...

Code: Select all

Sub RemoveFourLess()
Dim rData As Range, i As Long
Set rData = Range("B5").CurrentRegion
    Application.ScreenUpdating = False
    rData.Sort Key1:=rData.Cells(1), Header:=xlYes
    ActiveSheet.AutoFilterMode = False
    rData.Cells(2).EntireColumn.Insert
    rData.Columns(2).FormulaR1C1 = "=COUNTIF(RC[-1]:R" & rData.Rows(rData.Rows.Count).Row & "C[-1],RC[-1])"
    rData.AutoFilter Field:=2, Criteria1:="<5", Operator:=xlFilterValues
    rData.Offset(1).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    rData.Columns(2).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

[RENU1973]

But Rudi,I want to keep all the rows having documentno.more than 4 as I have mentioned earlier in my query.You have kept only one documentno.Please see once again requirement.

Thanks and Regards
Renu
Nepal

[Rudi]

Oops...sorry...I'll make the change to the formula to do that...
Will post back on a moment.

[Rudi]

Try this...

Code: Select all

Sub RemoveFourLess()
Dim rData As Range, i As Long
Set rData = Range("B5").CurrentRegion
    Application.ScreenUpdating = False
    rData.Sort Key1:=rData.Cells(1), Header:=xlYes
    ActiveSheet.AutoFilterMode = False
    rData.Cells(2).EntireColumn.Insert
    rData.Columns(2).FormulaR1C1 = "=COUNTIF(R" & rData.Rows(1).Row & "C2:R" & rData.Rows(rData.Rows.Count).Row & "C2,RC[-1])"
    rData.AutoFilter Field:=2, Criteria1:="<5", Operator:=xlFilterValues
    rData.Offset(1).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    rData.Columns(2).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

[RENU1973]

Thanks Rudi Sir for nice cooperation.It is my actual requirement.It will be learning lesson for me.

Thanks and Regards

Renu