Stop formula changing when deleting columns

[B-Man]

I have a few formulas like below that reference sheet "Data"
sheet1,B1 =COUNTIF(Data!A:Z,A1) sheet1,B2 =COUNTIF(Data!A:Z,A2)
etc
my issue is my macro runs and deletes columns out of sheet "Data" and edits my code to below and completely changes my formulas.
sheet1,B1 =COUNTIF(Data!A:[B]T[/B],A1)

I have tried adding the $A:$Z but that doesn't stop it either.
Any ideas?

 

[FormR]

Hi, you could try like this =COUNTIF(INDIRECT("Data!A:Z"),A1) which has the dis-advantage of being volatile - or you could have your macro re-set the formulas.

 

[B-Man]

I used a macro to re do my formulas thanks