Fastest solution sought: Open 103 xlsx files and remove blank cells from all sheets

[JackDanIce]

Hi,

I have 103 files all in the same folder, each file is approximately 30kb in size and contains 5 or less worksheets.

Task for each worksheet in each file is:

If A1 is not empty then delete each empty cell (entire row) found in column B (column A will define the last row position).

I wish to write a macro that is faster than using a nested loop and cycling through each sheet per file:

For Each file in Folder
   Open file
        For Each worksheet in opened file
              If worksheet.A1 <> "" Then 
                  LR = cells(rows.count, 1).end(xlup).row
                  cells(3, 2).resize(LR-2).Specialcells(xlcelltypeblanks).entirerow.delete    'Separate reason to ignore row 2 and start at B3) 
              End If
       Next worksheet
Next file

(Aware this is pseudo code, for purposes of posting question only)

One possible issue with Specialcells is there may be invisible characters in column B so my test may need to change to any cell in column B where length of cell contents < 2

Would an ADO connection be able to read and write/edit these files faster than having to open and close each one? I thought ADO connections were suitable for .csv files only or connecting to a database.

Any thoughts/comments/suggestions very welcome.

TIA,
Jack

 

[arthurbr]

I have no idea how to help with ADO. Perhaps this link will put you on track ? Erlandsen Data Consulting

 

[svendiamond]

Instead of (xlCellTypeBlanks).EntireRow.Delete could you try using AutoFilter, find blanks, then delete using (xlCellTypeVisible).EntireRow.Delete ?

However, either way this is going to be a slow macro. Deleting rows, combined with opening and closing 100 files is going to take a while regardless. Can you just filter the data rather than actually deleting the rows?

 

[mikerickson]

That is probably about as fast as the core routine will get. What will probably help the speed would be to turn off ScreenUpdating.

Application.ScreenUpdating = False
    ' your routine, including Open, loop through sheets, Close
Application.ScreenUpdtating = True

If your workbooks also have formulas, you could set that to xlManual.

 

[JackDanIce]

@arthur that link suggests ADO, thank you though can see a few suggestions from there to try.

@sven and @mike, good comments, thank you. Have used filters in past so yes will use instead of the inner loop over each row; the data in the source files is all static and range on each sheet is A1:G200 (or fewer rows) so turning off/on application settings (screenupdating, calculation manual, display alerts etc) will help and I'll include outside of the main loop. May also consider a helper column and sort/clear cells approach too.

It's the opening and file closing that seems to be the biggest bottleneck, hence considered using ADO to edit the files directly instead of needing to open and close them. Noticed in arthur's link though you can specify which sheet you want to work with; I thought it was only possible on a single sheet approach, hence .csv file type, so looking into this further. Will update with final code once written.