#REF Error After Macro

[geospatial]

I have a dynamic range, but after running a macro I end up getting a #REF error. My dynamic range before the formula looks like this =offset(Master!$A$2,0,0,counta(Master!$A:$A),1), and then after the macro it is =offset(Master!#REF!,0,0,counta(Master!#REF),1). The macro ran fine with no problems, but not sure how to keep it from doing this every time.

 

[njimack]

Does the macro delete row 2 of your Master sheet?

 

[geospatial]

It does, but then it adds new data back in. Everyday we have about 500 new records, but then there are also some that are added or changed from previous weeks. The easiest way I thought of to keep my sheet updated was to do a macro that removes everything and then reimports all the data again.

 

[njimack]

The easiest fix is to change your dynamic range formula to
=offset(Master!$A$1,1,0,counta(Master!$A:$A),1)

 

[Andrew Poulsom]

Try using:

=INDEX(Master!$A:$A,2):INDEX(Master!$A:$A,COUNTA(Master!$A:$A))

or clear the contents of A2 rather than deleting it.

 

[geospatial]

I will give both a shot. Thanks to both of you. I was either going to try that or to maybe do a macro that creates a temporary dynamic range while running the macro

 

[geospatial]

In a macro would that be selection.clearcontents?

 

[Andrew Poulsom]

In a macro would that be selection.clearcontents?

Yes.

 

[njimack]

In a macro would that be selection.clearcontents?

As a general rule, you don't ned to select cells to perform actions on them, so it would be something like
Range("A2").ClearContents

 

[geospatial]

would Range("A2") clear everything to the right and down of that cell then?