Change text to values and adjust column location

[Lonney]

Hopefully you can help me get this figured out!!

Please Login or Register  to view this content.

I am using the same code as above, but changed the columns. If I use the columns above, no issues. If I use the new columns (same data in the columns) Excel freezes up. Error: Excel cannot complete the task with available resources. Keep in mind, that if I run the same code on the same data, there are no issues. I only changed the column from ("GW:GW, JC:JE") to ("D:F"). Been at it for 3 hours, changing things around ect. to no avail!!!!

My Code:

Please Login or Register  to view this content.

[millz]

Try this instead:

Please Login or Register  to view this content.

[Lonney]

No good. It freezes Excel. This is really bizarre. The old code worked perfect and still does using the old cell values...but now that I changed the cell values, it does not.

If it helps, the entire code is below. I would appreciate any help.

Please Login or Register  to view this content.

[Lonney]

Here is something interesting. If I use the original code with the original cells, it works fine, no issues. But the only change was the range.

Please Login or Register  to view this content.

[millz]

Please post code within

Please Login or Register  to view this content.

[/NOPARSE] tags. You can still edit your previous posts to add them in.

If it is freezing with the code I suggested, most likely because you have too much data and formulas/conditional formatting/etc.

[Lonney]

Thanks for your help. I placed the code tags in my above post.
There is no conditional formatting or any other code, other than what is posted above. There are about 10k rows. The issue may be with data which cannot be changed to a number format? For example, there is a miss entry in one cell, which is a string, not a number.

[millz]

Thing is, it's not changing any "format", but to General type, so numbers will be treated as numbers, text as text.

Try copying your data to a new empty workbook and try the code.

[Lonney]

Ok. I am at work right now, but will as soon as I get home. With that being said, if I choose other columns; it works fine. Just odd. All I changed from the original code was the column information.

[JBeaucaire]

A couple of questions, are you using FORMULAS in those columns instead of flat values? The xlConstants reference won't see cells with formulas.

In my original version I used cDbl() which attempts to extract the numeric values from the text. You can also try replacing the with Val().

[Lonney]

Thanks Jerry. No formulas. Nothing changed other than the chosen columns. Instead of pulling in every row from the database; I ran a mysql quarry, only pulling in columns that I needed. I did this to reduce the size of the spreadsheet. Very odd. Your code still works flawlessly, but now on the wrong columns. If I replace with the new columns, Excel crashes.

[Lonney]

Is it possible, that now that there is less data/columns in the spreadsheet, the code needs to be paused, prior to moving to the formatting code? Basically, if the code is attempting to perform functions on the same set of data/cells at the same time, could this be the issue? I did disable background refresh with the same results. Just a thought. The code works flawlessly when using other columns.

[JBeaucaire]

You'd have to post a workbook with the macro that is misbehaving. It's all just guessing without it.

[Lonney]

Good news!! I gave in, created a new workbook - no issues!! The workbook must have been corrupt. Thanks again for all your assistance!!

[JBeaucaire]

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.