Step within Macro is taking forever to complete and locks up excel until complete

I have a need within my macro to automate a process of taking multiple columns worth of numbers and formatting them to be used outside of excel in a SQL query
So i need to take a list of numbers and format like so.
Original = 012945867
Expected = '012945867',

So my question is 2 parts.

#1 = How can this be setup to automatically get the data without user intervention, currently as you will see, when they get to this step, they are prompted to complete the data, BUT, Ive already setup to be pre-populated, but that still involves the user clicking thru the prompts.. So how can i do away with the prompts and just pass in the data

#2 = Once the above is accomplished, how can i make this process faster/ more efficient? Like i said, when this process runs, it takes minutes to run and this weeks test was only 212 numbers that it needed to format.

Any and all help/suggestions would be greatly appreciated.

would you care to show your code? Also how this query is executed - from excel?. what's the SQL statement
with 212 numbers it's not the #1 of the macro, I would say it;s waiting for the the sql query (or queries if you run let's say 212 queries).

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Can you post the current code?

The sql is not part of it, we are merely taking a list of numbers and formatting it so that it can be executed in SQL(manual step)
basically lists in columns like so

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Which step in the posted code is slowing things up?

I have a need within my macro to automate a process of taking a column worth of numbers and formatting them

You get what you ask for ...

Your AppendComma routine is reading and writing individual cells in the selection. Switching off screen updating would make that much quicker. That said, there doesn't seem much point having separate routines to format the non blank cells as text and then add a comma. Those routines could be combined.

My code reads all the data in column A into an array, processes it, and writes it back ... no interaction with the Worksheet. That code could easily be extended to process multiple columns AND add the comma (I would have thought.

Post a sample workbook with some typical data and before and after sheets.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

So, that's processing 210 x 16,384 cells = 3,440,640 cells


Whatever, try this way:

Please Login or Register  to view this content.

The problem with the code is in

Please Login or Register  to view this content.

and the range you are working with. So, you actually loop trough 210 columns or close to 3.5 million cells

Originally Posted by cubangt

thanks, i will try tomorrow when i get back to work. So the for each loop is quicker using range than selection? I mean i keep reading that selection is the wrong way to do certain tasks, but ok for others..

There is almost no use case that will require selecting things (like you do), but working with Selection maybe OK (e.g. if you want to allow user to select cells to process).
In this case however you end up selecting full 210 columns and start working with these columns

Anyway, I would not select, but work with the range directly
And next step could be to use ADO to access the database directly from excel, so no need for manual intervention at all

As Buran says, you should avoid selection in almost every case. In my solution, the first routine loads the data into an array, processes it, and writes it back to the sheet. The second routine does amend the cells on the sheet, however, it does not select the sheet or any of the cells.

Switching screen updating off before processing and on again afterwards will often improve performance where you are selecting cells.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome. Thanks for the rep.