CODEWITHSUNDEEP
excelvbaformula
Fah
Fah

Reputation: 203

How to delete duplicate rows but Keep CountIf value?

I am using a countif function on column E to get the number of times consumer number appears. When I use a code to delete duplicate the The value on Column E become 1.

enter image description here

enter image description here how can I delete the entire row of duplicates and not change the first value the countif gives me?

'column E test
     intrlastrow1 = Sheets("sheet1").Cells(Cells.Rows.Count, "B").End(xlUp).Row
     Sheets("sheet1").Range("E2:E" & intrlastrow1).Formula = "=Countif(A:A,$A$2)"

    With ActiveWorkbook.Worksheets("sheet1")
    Range("A2:B" & intrlastrow1).RemoveDuplicates Columns:=1, Header:=xlNo
    End With

What would be the most efficient way to count each value and paste the result into column E?

Upvotes: 0

Views: 195

Answers (1)

BigBen
BigBen

Reputation: 50162

If you want the values to remain unchanged, then hard-code the formula result before removing duplicates:

With Sheets("sheet1").Range("E2:E" & intrlastrow1)
    .Formula = "=Countif(A:A,$A$2)"
    .Value = .Value '<~ hard-code the result
End With

Upvotes: 2

Related Questions