Delete empty rows using VBA - MS Excel

Question

I am looking to see if there is a more efficient way to achieve the result below, so it can be extended if needed.

I'm using this to clean up large spreadsheets that have the rows C-Z blank. I imagine there should be a way to clean it up so that it doesn't have to double in size if I need to clean up a spreadsheet with data from C to AZ.

It's been a while since I used VBA, I found the code below online. (counting ROW B as the spreadsheet in question had an empty ROW A)

Sub delem()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 1 Step -1
    If Range("C" & r).Value = "" And Range("D" & r).Value = "" And Range("E" & r).Value = "" And Range("F" & r).Value = "" And Range("G" & r).Value = "" And Range("H" & r).Value = "" And Range("I" & r).Value = "" And Range("J" & r).Value = "" And Range("K" & r).Value = "" And Range("L" & r).Value = "" And Range("M" & r).Value = "" And Range("N" & r).Value = "" And Range("O" & r).Value = "" And Range("P" & r).Value = "" And Range("Q" & r).Value = "" And Range("R" & r).Value = "" And Range("S" & r).Value = "" And Range("T" & r).Value = "" And Range("U" & r).Value = "" And Range("V" & r).Value = "" And Range("W" & r).Value = "" And Range("X" & r).Value = "" And Range("Y" & r).Value = "" And Range("Z" & r).Value = "" Then Rows(r).Delete
Next r
End Sub

Thanks!

Comintern · Accepted Answer · 2015-06-27 19:47:46Z

2

Just add an inner loop to go through the columns you care about. This will actually run much faster, as VBA doesn't short-circuit the If statement (all of the conditionals are evaluated). But with the loop, you can exit early if you find a value anywhere:

Sub delem()
    Dim last As Long
    Dim current As Long
    Dim col As Long
    Dim retain As Boolean

    last = Cells(Rows.Count, "B").End(xlUp).Row
    For current = last To 1 Step -1
        retain = False
        For col = 3 To 26
            If Cells(current, col).Value <> vbNullString Then
                retain = True
                Exit For
            End If
        Next col
        If Not retain Then Rows(current).Delete
    Next current
End Sub

answered Jun 27, 2015 at 19:47

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Nergaard Over a year ago

Worked a charm, seemed to work just as fast, there was like 3000 rows to get rid of so not expecting it to go in a flash! But it looks a lot cleaner and thats what i was after :)

joehanna · Accepted Answer · 2015-06-27 21:52:25Z

0

The Excel worksheet function COUNTA is a clean way to test if a range is empty.

Sub delem()
  Dim lr As Long, r As Long
  lr = Cells(Rows.Count, "B").End(xlUp).Row
  For r = lr To 1 Step -1

    'This function Counts the number of cells that are not empty
    If WorksheetFunction.CountA(Range(Cells(r, 3), Cells(r, 26)) = 0 Then 
      Rows(r).Delete 
    End If

  Next r
End Sub

answered Jun 27, 2015 at 21:52

joehanna

1,4891 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

Delete empty rows using VBA - MS Excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related