Help! Newbie here. Macro functions but extremely slow.

Hello All!


I have had so much help with online forums and thank everyone for their help and advice. I was wondering if someone could help me with my macro. I am a civil engineer with no programming knowledge. Most of the vba macro I created is by using codes from this forum or by recording in excel and extending it a little further. It has worked so far however it takes hours and hours to process all the files. I now have a folder with over 600 files that I have to process and I have let it run overnight and it has successfully however I cannot present it to the users since it is time consuming. Couple of specific issues that I as a non-programmer can mention is:


1. Module 37 is the master list of all the macro I use --- I dont think this has any issues
2. Call one is pretty straight forward - it imports all the csv like files into the current workbook -- Don't think this has any issues
3. Call two (module 6) - calculates average speed and uniformity metrics. --- This is the one I think has major issues. Since the data is variable, I did not know how to mention "check for rows with values and calculate average". Instead, I calculate average of full columns or sometimes specific cell numbers which in this case I set it as 3600 rows. Then all the calculations are done cell by cell since I recorded macro and generalized. This macro takes a ton of time.


4. Module 29 also is recorded and is time consuming.


5. Module 33 is also recorded and takes time to copy and paste formula cell by cell.




I was wondering if anyone out here could help/ guide? I have attached the macro for your reference. Thanks in advance!


Sarah

WOW.... you didn't allow much time for an answer from here before posting the same question at MrExcel did ya.

rules on both forums will refer you to this http://www.excelguru.ca/content.php?184

Sorry!

I'm sorry. I had not read the rules. I will take this post down now. Thanks for pointing out!

I cannot figure out how to delete my post. Please report my post to the moderator so they can delete it. Thanks!

You don't need to take it down or delete it.
In fact your Excel questions and problems can help others, that's the idea of these forums.
If you or anyone feels it is necessary to cross post there is a proper way and that is to include links to the question in the other forums where the question has been posted.

You don't have enough posts on this forum to post links so this is the cross posts that I know of

http://www.mrexcel.com/forum/excel-...cations-newbie-need-help-efficient-macro.html
http://www.excelforum.com/excel-pro...-here-macro-functions-but-extremely-slow.html
http://www.ozgrid.com/forum/showthread.php?t=191169

if there are more please add them. You will need to omit http://www. part for the forum software to accept them prior to having 5 or 6 posts.

Thanks

USING RC notation is exceedingly difficult to edit and find out what you are wanting to do. Suggest dump a heap of comments everywhere

1: Set Autocalculate off at the start and on again when you have finished
Application.Calculation = xlCalculationManual and
Application.Calculation = xlCalculationAutomatic

2:The main reason for the slowness is that you are activating each sheet and then setting a ton of formula cell by cell.
Take Module6 Sub Two_Avg_Speed_Loop() as an example

a: don't activate the sheet
b: don't select the cell(s)

I would suggest


3: Also make sure that you release memory (in case it's a memory problem)
in the above need set range1 = nothing at the end etc

Filling whole columns - containing 2^20 cells- with formulae is madness.

Total agree snb and it's not just one sheet.
Was merely pointing out if you absolutely had to progress down that path where the biggest bottle necks are

I agree that the macro is the most inefficient one anyone would come across. It just stated with couple of scenario and now that it works, I need to make 600 files run. That is why all the cells have selection and formula and paste etc. I just recorded macro in excel and applied to all worksheets in a workbook. Now it need to find ways to make it efficient and set an approximate timeline. Do you think putting comments and the letting you all know what I am trying to accomplish at every step of the macro code would help you all give me suggestions? As always, Thank you all for your time and help!

--Sarah

Sarah, it's more the size of the project. A simple read thru and make some suggestions is typically about ten minutes for a question. To go thru 1000+ lines of code, understand what is going on is about 4+ hours. I've also got tons of legacy code written by others and often there isn't the time budget to properly fix things so it's a case of what minor modifications can help the most or does it need to be dumped and rewritten.

Some other suggestions for you are
1: Dump it into pivot tables add file, year, month, day, hour, minute (as required) columns
2: Create a CSV sheet, a calculation sheet and a summary sheet so

erase summary
for each CSV
a) erase CSV sheet
b) Read CSV and memory dump array into CSV sheet

something like
lrow = ws.Range("a1").End(xlDown).row
lcolumn = ws.Range("A1").SpecialCells(XlCellType.xlCellTypeLastCell).Column
oData = ws.Range("a1").Resize(lrow, lcolumn).Value2 'dump sheet to array and

'dump back as
Sheets(sSheet).Cells(iPacked, 1).Resize(UBound(oNewData, 1), UBound(oNewData, 2)).Value2 = oNewData


c) Copy Results into summary (again memory dump as above)

d) also use memory not cells, again my example above
read oData
redim oNewData(same rows but more columns if required)
for each row populate all oNewData columns

Thank you all for the help and advice. I appreciate it. I will try everything mentioned here and see how it works. Thanks again for your time !