Copy Formula down thousands of rows

[Batman77]

Hi Team,

Looking to know if there is an easier way to copy down a formula through 100K+ of rows without causing excel to give up and die quietly.

For example I am using this formula =IF(COUNTIF($Q$2:$170621,Q170621)>1,"Y","N")

Any help will be greatly appreciated as I have a bit doing this long way copy 1-2k at a time

 

[Batman77]

Correction to formula used above is: =IF(COUNTIF($Q$2:$Q170621,Q170621)>1,"Y","N")

 

[Batman77]

Hello? Anyone?

 

[mirabeau]

Hi Team,

Looking to know if there is an easier way to copy down a formula through 100K+ of rows without causing excel to give up and die quietly.

For example I am using this formula =IF(COUNTIF($Q$2:$170621,Q170621)>1,"Y","N")

Any help will be greatly appreciated as I have a bit doing this long way copy 1-2k at a time

will this do?

Sub fillcells()
Range("B2:B" & 170621) = "=IF(COUNTIF($Q$2:$Q170621,Q170621)>1,""Y"",""N"")"
End Sub

 

[Batman77]

Thanks Mirabeau However this does not work unfortunately

So the formula should continue as a series down the column as in:

Cell X2=IF(COUNTIF($L$2:$L2,L2)>1,"Y","N")
Cell X3=IF(COUNTIF($L$2:$L3,L3)>1,"Y","N")
Cell X4=IF(COUNTIF($L$2:$L4,L4)>1,"Y","N")
...all the way down to whatever the last cell is, which in this case is: Cell X174281=IF(COUNTIF($L$2:$L174281,L174281)>1,"Y","N")

I am not that good with VBA so not sure how one would be able to tweak your suggestion.