Inefficient/slow data cleanup code

Hello again,

I'm relatively new to VBA for Excel, and I've been learning as I go along. I don't know all the methods available to me, and I don't know which are more effecient than others. I was hoping somebody could take a look at the code below and let me know if anything could be significantly improved. I know it's a mess; it looks like the originator just used excel's built-in macro recording feature and added some basic looping code. I've added to it as my job has necessitated, but it's just become sloppier and unwieldy.

The macro's basically a clean-up script to turn our company software's garbled reports into something readable by humans. The reports come in two flavors, Detail and Profile. The main difference between them is the order of the data columns, and in which column the usable data begins. What I'm calling "usable data" begins with a cell containing either " Master :" (in a Profile report) or "Master - " (in a Detail report). The actual usable rows of data may begin in column A or J (in the case of a Detail report), or column A or G in the case of a Profile report. (It's possible that a Profile report's data could start in column I, but I haven't seen this since we stopped using an older version of our software, so I'll assume it won't happen in the future.)

This macro moves the start of the data in each row from whatever column it's currently in, to column J. Sometimes a row contains no usable data and is deleted, shifting up the rows underneath. Once all data is aligned in columns, the columns are shifted around a bit to put them in the desired order.
There's no real reason, as far as I can tell, that it's moved to column J... this is just how the macro worked when I started updating it. If you think data should first be moved to column A, that's fine, I just haven't re-written it that way so that I wouldn't have to re-write the bits that shuffle the columns later on.

I don't expect anybody to re-write any portions or anything, but a simple "Try using this method instead of that" would be greatly appreciated.

Thank you!

Please Login or Register  to view this content.

Thank you. I was able to generate two small reports and remove the sensitive data.
The reports named "___ report - __ items" are what our software spits out, and the ones with "cleaned" in the name are the ones that've had the macro run on them. Each should contain 14 rows of data when 'cleaned' and two rows do not have anything in the "Description" column; this is intentional.

With these reports being as small as they are, they may not exhibit all of the strangeness that the larger reports can exhibit, but I'm sure I can adapt whatever suggestions you might make to fit my data.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hmm, I'll try, but I'm not really having a problem to be described in the title. I'm just wondering if the code could be improved, and if so, how it could be done.

Something like "Improve code's efficiency"

Also, explain what you want to do because just a quick glance at the code & I can see that it is extremely inefficient. Stating the purpose of the code might result in a better solution than just amending what you ahve

Originally Posted by royUK

Something like "Improve code's efficiency"

Also, explain what you want to do because just a quick glance at the code & I can see that it is extremely inefficient. Stating the purpose of the code might result in a better solution than just amending what you ahve

Done! (I hope)

The title still does not comply with Rule #1. I've given you an idea of how to title it

Thank you! You have an interesting style of writing your VBA code...

While the code you provided certainly works on those small reports I uploaded, I'm having mixed results with the actual reports I deal with, but maybe I'm applying your macro incorrectly. Detail reports are fine (awesome!), but Profile reports are not.

Most of the time, the Profile reports are ending with Type Mismatch errors on the "zctrl = WorksheetFunction.CountA(WorksheetFunction.Index(a, i, 0))" line. Sometimes the macro runs, but the report is mangled. I don't know if it's because the actual reports I'm running it on are .CSV files, or if it's because they can reach around 20,000 rows, though most are about 400 rows. It doesn't seem to matter if I copy your module to my reports, or if I copy the contents of my reports to your workbook.

Originally Posted by teitoku

While the code you provided certainly works on those small reports I uploaded, I'm having mixed results with the actual reports I deal with, but maybe I'm applying your macro incorrectly. Detail reports are fine (awesome!), but Profile reports are not.

Most of the time, the Profile reports are ending with Type Mismatch errors on the "zctrl = WorksheetFunction.CountA(WorksheetFunction.Index(a, i, 0))" line.

I have made some changes to Certificate_report, please check on real file. Run "Report_ready"

Originally Posted by watersev

the main principle is: if do actions with cells on a sheet it will take long time if your data is measured by thousands. Yes, we put all data to array and manipulate it there and then put it all back in one go. It works much faster even with huge data sets. When I tried to measure time for both codes I got:
yours: 0,23 s
my: 0,06 s
and we have only 16 rows of data. New macro is nearly 4 times faster.

Very interesting!

What is the reason for putting the Detail and Profile code into their own subroutines and declaring variables in those subroutines, rather than a "select case" as I had? Is there a speed or memory advantage here, or is this just good programming practice I should follow?