Deleting multiple blank cells in a large excel spreadsheet

Question

So I have a dataset that's about 7000 x 7000 and it contains a lot of blank cells (probably about 70% of the cells are sparse). This was due to the way in which I exported my data from a java program I scripted. I thought I could simply remove all the blank cells by selecting all blank cells and deleting them. However, due to the size of the dataset, trying to do this causes excel to crash everytime. Besides manually performing this task on each individual column, is there any other way I can over come this?

It's not clear exactly what you want the end result to be - do you want to delete all empty rows/columns? — Tim Williams
I basically want to remove all blank cells in rows/columns, however I won't have like an entire row/column that is empty (i.e. they may contain a few values, but for cells that are blank I want them removed). And yeah Go To Special -> Blanks is too much for my computer lol, and I'm running 64bit windows on a quite a good spec computer — user2062207

Tim Williams Tim Williams · Accepted Answer · 2015-03-04T22:15:12

Sub Tester()

    Dim rng As Range, c As Range, b As Range

    Set rng = Range("a1").CurrentRegion.Columns 'assuming no fully-blank rows/cols
    For Each c In rng.Columns
        On Error Resume Next 'skip error if no blanks
        c.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
        On Error GoTo 0
    Next c

End Sub

Deleting multiple blank cells in a large excel spreadsheet

1 Answers