# How to make Countif with a Dynamic Range

#### Luchano

##### Board Regular
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!!!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### NeonRedSharpie

##### Well-known Member
Code:
``[COLOR=#333333]=IF(COUNTIF(F:F,F2)>1,"Remove",0)[/COLOR]``

#### Luchano

##### Board Regular
Code:
``[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

##### Well-known Member
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

##### Board Regular

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

##### Well-known Member
Code:
``````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

##### Board Regular
Code:
``````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!!!

Replies
2
Views
63
Replies
7
Views
94
Replies
5
Views
102
Replies
17
Views
171
Replies
3
Views
41