CODEWITHSUNDEEP
c#excelworksheet
SamAPOC
SamAPOC

Reputation: 73

Remove Blank Rows from an Excel Worksheet

I've done some searching but I can't seem to find a quick method to get rid of all BLANK rows within a worksheet. I don't want it to take up much time. Is there such a way?

I've tried a few methods that aren't brilliant that involve filtering blanks in all columns then deleting what's on screen. Maybe there's a function in excel that I'm missing here?

I'm looking for something like the UsedRange.RemoveDuplicates() function that did a lot of work in a second or two.

Upvotes: 0

Views: 4511

Answers (4)

JamesFaix
JamesFaix

Reputation: 8655

Iterating through each row takes forever, but that's only because of the FOR loop overhead. The main operations WorksheetFunction.CountA and Range.EntireRow.Delete really do not take very long.

The best solution I've found is to start with chunks of 100K rows and delete all that have no values, then move down in powers of 10 until you're at the single row level.

This is fast whether you are trying to remove excess whitespace around your data or removing single rows sporatically within your main data range.

Public Sub removeblankrows()
Dim ws As Worksheet: Set ws = ActiveSheet

With WorksheetFunction
    Dim pow As Long

    For pow = 6 To 0 Step -1

        Dim chunkSize As Long: chunkSize = 10 ^ pow

        Dim chunks As Long: chunks = (ws.UsedRange.Rows.Count \ chunkSize) + 1

        Dim c As Long
        For c = chunks To 1 Step -1
            Dim last As Long, first As Long

            last = .Min(c * chunkSize, ws.Rows.Count)
            first = .Max(1, (c - 1) * chunkSize)

            Dim c1 As Range, c2 As Range, rng As Range
            Set c1 = ws.Cells(first, 1)
            Set c2 = ws.Cells(last, ws.UsedRange.Columns.Count)

            Set rng = ws.Range(c1, c2)

            If .CountA(rng) = 0 Then rng.EntireRow.Delete

        Next c

    Next pow
End With

End Sub

Upvotes: 1

lori_m
lori_m

Reputation: 5567

Here's two methods to delete all blank rows on the active sheet...

1 VBA. This way should be much quicker than looping through rows:

cells.RowDifferences(cells(rows.Count,columns.Count)).rows.Hidden=true
cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
cells.Rows.Hidden=false

2 Keyboard. This way is just three steps in the undo list, so can easily be undone / retraced.

enter image description here

Step 1. Select data cells. Click left corner heading to select all cells then press Shift+Tab Ctrl+\

Step 2. Hide and delete remaining rows. Press Ctrl + 9 Ctrl + A Alt+; Ctrl + -

Step 3. Unhide selected rows and select first cell. Press: Ctrl + Shift + 9 Ctrl + Home

Before

enter image description here

After

enter image description here

Upvotes: 2

bendataclear
bendataclear

Reputation: 3850

For very large datasets (100k+ rows) I've always found it massively quicker to sort by blanks then remove than to filter and remove lots of separated lines. If the blank rows are spread out throughout the file it can be between 5 and 10 times quicker.

Upvotes: 1

evilkos
evilkos

Reputation: 597

If you need for your functions to work fast in excel you should set global Excel application variables, such as "Visible = false", "ScreenUpdating = false". Filtering the blanks and deleting them should be the fastest approach, although I would go with manually making the list of the rows to delete and then deleting them in one call

RowsToDelete = "2:2;4:4;8:8";
Range = Sheet.Range()
Range.Delete()

Upvotes: 1

Related Questions