Code for Deleting (Somewhat) Blank Rows for Extremely Large Workbook

Asked Feb 21 '20 at 23:22

Active Feb 21 '20 at 23:57

Viewed 41 times

I recently received a 500k row excel from an accounting database. The current file is unusable in its current format and too large. I'm hoping to slim down the excel by deleting blank spacer rows that are in between every row with actual numbers (except these spacer rows have three cells that contain abstracts (e.g., formulas that spit out " ") in it that prevent the row from being completely blank).

I've tried running the following code, but since the file is so large the excel crashes when I run the macro. I am aware of using SourceRange = Application.Selection, but is there away of creating a function that I can run a macro and it will continually delete say 25k rows at a time as oppose to manually selecting the range or using the following code below.

What would be the best method?

Sub DeleteBlankRows()

Dim x As Long

With ActiveSheet

    For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        If WorksheetFunction.CountA(.Rows(x)) <= 3 Then
            ActiveSheet.Rows(x).Delete
        End If
    Next

End With

End Sub

edited Feb 21 '20 at 23:57

Marx

asked Feb 21 '20 at 23:22

WCRider

3

I've had a similar issue and instead of writing a macro, would it instead be easier to filter out blanks and copy the useful data to a new workbook? – Marx Feb 21 '20 at 23:29
1

[this might help](https://stackoverflow.com/a/58461754/445425) – chris neilsen Feb 22 '20 at 00:13
1

[or this](https://stackoverflow.com/a/14245591/445425) – chris neilsen Feb 22 '20 at 00:24

Code for Deleting (Somewhat) Blank Rows for Extremely Large Workbook

0 Answers0