Tables Causing Crashes

My data is in a table (ListObject) of 46 columns and about 6000 rows. My macros mostly add data to certain cells.

Starting yesterday, the macros would stop running at random points. Eventually I realized that whenever a cell is selected or its value changed, there's a small chance that the macro will quit with no error message.

To test this, I ran this simple macro to select every cell in a column and highlight it:

Please Login or Register  to view this content.

Each time I ran it, it would highlight more cells -- the first time around 30, the next time around 70, etc. -- but it always stopped long before 6000. The results were never consistent.

I tried copying all the data into a new workbook, and that worked for a while. (I meant to copy it as a range, not a table, but I frankly don't remember.)

When I convert the table to a range, this problem doesn't happen, but it returns when I change it back. It also happens to another table of similar size which is derived from the main one.

Converting the data to a range, and changing the macros accordingly, would be a very big job and I have reports to get finished before I can tackle it. I had eventually planned to use the method of importing the whole table into an array, and processing it there, but that's a big job too.

Any idea what could be causing the corruption? And more to the point, how to get the macros to keep running?

Originally Posted by humdingaling

try this and see what the outcome of the debug
...

does it go all the way thru and list all the cells in the range?

When I tried your test, yes, it went all the way through.

When I added the "Debug.print" line to my own test macro, it stops as before after a few dozen lines. The Immediate window shows the address of the cell where execution stopped.