How do I stop a formula from changing??

[justme]

=COUNTIF($D$8:$D$120,1)

I have this formula in cell E1. I also have a macro which deletes rows that have blank cells. When I run the macro, the $D$120 keeps changing (reducing). How can I get the cell to ALWAYS remain the same? I want it to be absolute, not relative.

Thanks.[/b]

 

[justme]

Range("h8").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R8C3:R120C3,g8)"

Here's another instance that is not working. When the macro runs the formula changes the ,g8 to ,'g8' and only returns a 0 value.

What is the work-around for this situation?

 

[Norie]

The reason it's changing it to 'G8' is because you are using A1 notation for G8 and R1C1 notation for the rest of the formula.

What is the actual formula in A1 notation?

Range("h8").Formula = "=COUNTIF(C8:C120,G8)"

 

[justme]

That was it. Thanks.