Can someone help speed up my code?

I have a spreadsheet with travel information and I wrote code to loop through and find the specific columns I am looking for and then analyze them based on the criteria provided. I am relatively new to VBA and I am assuming there is a faster way to do this. Currently it takes about 69 seconds to execute. I dont want to use specific columns in case someone deletes a column that isnt needed. Any suggestions? Thanks

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I am using .rows to find which row my text is in. I am trying to avoid putting row and column numbers in because the data contains a lot of misc info and if somone deletes a column it will cause errors. I am thinking maybe if the code is restructured and I am able to clear some of the memory this will speed things up but I am not sure how i would do it. I will try .usedrange in the mean time to see if this makes a difference.

Hi

In addition to what has already been said, you are doing far too much reading and writing.
Every time you read from the Sheet, and every time you write to the sheet you are incurring an enormous overhead in crossing the VBA / Sheet interface.
You should read all of your data into an array.
You should then dimension an output array of the appropriate size to take your result.
All of the processing of the array should then happen within VBA, and only when you have your Output array, should it then be written back to the sheet with one single command.

That would speed up your code by a huge magnitude.

Thank you for the information. Can you show me an example of how to do this with one of the loops in the above code? I am not sure how to tackle this. I have been researching it but still cant seem to get it. I broke the above code into 3 sections instead of one and it only sped up by 5 seconds so that did not do me any good.