instead of using VBA have you looked at the DSUM, DCOUNT formulas?
By setting a criteria cell you can calculate info within a particular range.
Also, you can write over the old data and the range will remain the same.
Not sure if it'll help you, but it's a thought!

"tony33r" wrote:

I am trying to write the VBA to determine the ranges of cells that pass a
certain criteria. Let's suppose that there is a set of data of 10 units, the
units that failed are 3, 4 and 8. I want to create a string that will look at
the pass units and generate a text like this: Units 1 through 2, 5 through 7
and 9 through 10 passed. Of course, the data might change with a new subset
of units, and the number of units tested can vary. I have succesfully
imported a text file that contains the data, analyze the data based on
pass-fail criteria, used an array to store the fail values (such as
fail(1)=3, fail(2)=4, fail(3)=8, total units inspected 10, totfailed
units=3), but I am struggling with the code to create such pass data string.
Any help will be very helpful.