Code Efficiency [77 files, 1.04MM lines, 647MB]

[gwebb]

I'm getting started with VBA. I'm running Excel 2007. Looking to improve this code so that it might run faster. At current speeds it'll take 15+ hours (ran through 18MB out of 647MB in about 30 minutes).

I have 77 different files I've renamed "1" through "77". Each file is set up as a data base with records in rows A through AF. For each record of each file I want to look in row N, and if it is a certain value add the entire record to a singular file. Here is the code I created. I believe there are efficiencies that could be implemented, I just don't know what they would be, being a newb. Any help would be appreciated. The code is below. Thanks!

Please Login or Register  to view this content.

[tigeravatar]

gwebb,

Welcome to the forum!
In the future, please wrap your code in code tags (link in my sig for how).
As to your question, give this a try:

Please Login or Register  to view this content.

[romperstomper]

Avoid selecting and activating anything. If you loop through the cells and build up one range variable using Union and run copy/paste that in one go for each workbook it will be faster. You can also use an autofilter and then just copy visible cells across.
I'm on a phone at the moment but will try and post code later if someone else hasn't already jumped in.

[gwebb]

Thank you for the warm welcome.

This looks to be a great start. As I go through the program piecewise it seems that after the autofilter is applied, once the line

.AutoFilter 1, Array(3, 4, 9, 13, 15), xlFilterValues

is executed all the lines in the 1File.xls are hidden but then not pasted in ThisWorkbook, and nothing particularly noticeable happens after

.Offset(1).EntireRow.Copy wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)

is executed. Any thoughts?

[tigeravatar]

My apologies, made a mistake on the autofilter line. Each number should be surrounded in quotes "3", "4", etc. Here's the updated code:

Please Login or Register  to view this content.

[gwebb]

Actually, in looking at it further I am having issues with the command line that is doing the pasting

.Offset(1).EntireRow.Copy wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)

It seems to run fine until 1File is 10, then it pastes over previous data (it actually resets and pastes starting over at row 2, and there is an empty row after the data-- it's definitely writing/pasting an empty row, if that matters).

[tigeravatar]

The pasting an empty row is intentional, to avoid errors with specialcells (which is a method I'm not using). If you'd rather the empty row not be pasted, that can be amended, but generally speaking it shouldn't matter. Here is updated code so that the paste location is based off wsDest column N instead of wsDest column A:

Please Login or Register  to view this content.

[gwebb]

This worked perfectly, can't thank you enough.

I would like to ask for my own edification, given that I had to go through and rename 77 files for this procedure to work, what would be an example of some code that could change the names of the files in a given folder, or a For/Each Loop that could go through the files without renaming them, etc. Essentially, was this the cleanest way to go about this solution?

[tigeravatar]

gwebb,

There are a couple ways to loop through each file in a folder, there's the Dir() method and the FileSystemObject (commonly shortened to FSO) method. I prefer FSO, and here's what it would look like:

Please Login or Register  to view this content.