CODEWITHSUNDEEP
vbaexceldelete-row
Cassidy
Cassidy

Reputation: 9

Excel Macro / VBA - How do I delete an entire row if a cell in one column is blank?

I want a macro code that (1) starts at Row 3, (2) and deletes any row where its cell in Column B is blank. I've tried... 

Sub DelBlankRows()  
    Columns("B:B").Select  
    Selection.SpecialCells(xlCellTypeBlanks).Select  
    Selection.EntireRow.Delete  
End Sub

And 

Sub delrows()  
     Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete  
End Sub

Upvotes: 0

Views: 12390

Answers (2)

grug.0
grug.0

Reputation: 355

Chances are, you are going to be looping this in something... The concise answers given to you will not "update" the row index of your loop. I present a cleaner looking option that will likely fit into whatever you are doing:

pseudo CODE

For row = start To rowcount
    If cells(row,column).Value = "" Then
        Cells(row,column).EntireRow.Delete shift:=xlUp
        row = row-1  'this is because the xlUp shift changes index for all rows BELOW your loop
    End If
Next row

Upvotes: 0

Rik Sportel
Rik Sportel

Reputation: 2679

Better would be to define the worksheet as well:

Sub test()
    Worksheets("sheet1").Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
End Sub

Will delete every row where column B is empty.

If you need to start at row 3 until the last row:

Sub test()
    Dim lr As Double
    lr = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
    Worksheets("Sheet1").Range("B3:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
End Sub

Upvotes: 1

Related Questions