Replacement for the Flawed UsedRange Property

[Excel Fox]

If the sheet actually contains information that is represented as colored cells (a traffic light dashboard for example), with no real content within any (or most) cells, the find method would not solve the purpose.

As far as UsedRange is concerned, I for one wouldn't consider its way of working as a flaw. A usedrange should be a usedrange where there is clearly no ambiguity in saying that any cell that was performed an action on is a used cell. And any rectangular area containing used cells will be the used range of that sheet.

So then it comes down to how intuitively the developer wants to look at covering any potential issue (or flaw as Rick puts it) in the way UsedRange works.

[Rick Rothstein]

If the sheet actually contains information that is represented as colored cells (a traffic light dashboard for example), with no real content within any (or most) cells, the find method would not solve the purpose.

That is an excellent point and one I had overlooked.

As far as UsedRange is concerned, I for one wouldn't consider its way of working as a flaw. A usedrange should be a usedrange where there is clearly no ambiguity in saying that any cell that was performed an action on is a used cell. And any rectangular area containing used cells will be the used range of that sheet.

So then it comes down to how intuitively the developer wants to look at covering any potential issue (or flaw as Rick puts it) in the way UsedRange works.

I guess it comes down, as it always should, to the programmer using the right (coding) tool for the right job. Perhaps the real flaw in the UsedRange property is that it does not have an optional argument allowing you to customize the type of usage you want it to calculate... constant data plus displayed data from formulas, constant data plus formulas no matter what they display, any used cell no matter how it is used, or maybe even some others that I have not thought of yet... but I still think it should be fixed to not remember areas that are no longer in use.

[tfurnivall]

Rick and ExcelFox

It has been instructive to read your debate. However, neither position really covers the need that prompted my request recently. If I delete a set of rows from the worksheet, I would expect to see that deletion - somehow - reflected in UsedRange. Speifically, I am doing the following.

During a process I add occasional new rows to a worksheet - varying number of new rows representing Transactions, Merchants and LookUp entries.

During testing I need to be able to delete these entries so that I can re-run a test. Initially this is a manual process (which seems to work OK). I decided that I needed to have a way of doing it more easily - and at any rate, there are conceivable times when this will be necessary. SO, enter the cmdResetTransactions, cmdResetMerchants and cmdResetLookUp buttons. I used the macro record to 'copy' the code that Excel claims to use and was amazed to discover that there must be something more!

Research indicated that qualification was important (rather than active sheet, or simply a sheetname, I should qualify right up to the Application level). Tried this - still no luck. UsedRange would frequently indicate that some part of the sheet (which I had deleted,using EntireRow) still contained data. SInce my goal is to be able to re-use cells in a predictable manner, this behavior was unacceptable. That's my gripe with UsedRange - when a cell - or a row - has been deleted, it seems that sometimes, under unspecified conditions, it is still, in a sense, there.

Thanks, Rick for your UsedDataRange function - this will probably meet my needs. But I wish that MS would create real, usable documentation for their products, not the half-baked trash that they pump out.

(End rant!)

Thanks for the great work you guys are doing with this forum!

Tony