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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?
 
Upvote 0
That is probably about as fast as the core routine will get. What will probably help the speed would be to turn off ScreenUpdating.

Code:
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.
 
Upvote 0
@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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top