How to make Countif with a Dynamic Range

[Luchano]

Hi all!

i'm using the formula below to find duplicates; however, i want to make the formula dynamic, so i don't have to change it everytime new data is added to sheet. Any help will be greatly appretiated.


=IF(COUNTIF(F2:F1774,F2)>1,"Remove",0)


Thank you!!!

 

[NeonRedSharpie]

[COLOR=#333333]=IF(COUNTIF(F:F,F2)>1,"Remove",0)[/COLOR]

 

[Luchano]

[COLOR=#333333]=IF(COUNTIF(F:F,F2)>1,"Remove",0)[/COLOR]

Thank you, but that's not quite what i'm looking for. When you refer to an entire column it creates errors over large sums of data, and it assignes "Remove" to rows that shouldn't be removed.

 

[NeonRedSharpie]

I've never had that issue before...unless you have data beneath your data set...

If you don't want to use the entire column, your only option for a dynamic formula is to input it using VBA.

 

[Luchano]

I've never had that issue before...unless you have data beneath your data set...

If you don't want to use the entire column, your only option for a dynamic formula is to input it using VBA.

Ok, so what would be the code?


Thanks for your quick response!!!

 

[NeonRedSharpie]

Sub dynamicRange()

    Range(Cells(2, 7), Cells(Cells(Rows.Count, "F").End(xlUp).Row, 7)).FormulaR1C1 = _
        "=IF(COUNTIF(R2C6:R" & Cells(Rows.Count, "F").End(xlUp).Row & "C6, RC6) > 1, ""Remove"", 0)"


End Sub

Change the two instances of 7 for the column where the formula will go. 7 assumes column G.

8 = H
9 = I
10 = J

etc...

 

[Luchano]

Sub dynamicRange()

    Range(Cells(2, 7), Cells(Cells(Rows.Count, "F").End(xlUp).Row, 7)).FormulaR1C1 = _
        "=IF(COUNTIF(R2C6:R" & Cells(Rows.Count, "F").End(xlUp).Row & "C6, RC6) > 1, ""Remove"", 0)"


End Sub

Change the two instances of 7 for the column where the formula will go. 7 assumes column G.

8 = H
9 = I
10 = J

etc...

Thank a lot NeonRedSharpie!!!