PDA

View Full Version : If cell is blank, delete the row?



doctortt
11-25-2013, 08:56 AM
Hi there,I'm new to this. I have been reading this newbie VBA book, but I still can't figure this out. Can someone please help? What I want to do is the following:Check all cells in column B and if a cell is blank, delete the entire row.

doctortt
11-25-2013, 09:49 AM
I got this to work

Sub Delete_Rows()
Dim i,
LastRow
LastRow = Range("E" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "E").Value = "" Then
Cells(i, "E").EntireRow.Delete
End If
Next
End Sub

doctortt
11-25-2013, 09:51 AM
By the way, how do I post my codes here so that it's readable?

mancubus
11-25-2013, 01:06 PM
hit the # button in quick reply panel. code tags ([ CODE ][ /CODE ]) will be inserted in the message. copy the macro between these tags.

BTW, i couldn't get the line breaks and some other special chars printed in VBAX threads in my office computer. whenever i copy the codes all line breaks disappear as in your second post.

mancubus
11-25-2013, 01:20 PM
Sub Delete_Rows()

Dim i As Long, LastRow As Long

LastRow = Range("E" & Rows.Count).End(xlUp).Row

For i = LastRow To 1 Step -1
If Cells(i, "E").Value = "" Then
Cells(i, "E").EntireRow.Delete
End If
Next

End Sub


above code loops through the last non blank cell to first cell backwards in column E.

last non blank cell's row number is assigned to LastRow variable and Next statement makes this number increment by -1 (or decrement by 1) (because of Step -1).

If Cells(i, "E").Value = "" statement checks if the cells between LastRow and 1 are blank and ih the condition is true row of the blank cell is deleted.

so if you want the delete rows based on Column B, change If Cells(i, "E").Value = "" to If Cells(i, "B").Value = "".

Paul_Hossler
11-25-2013, 07:43 PM
Another way



On Error Resume Next
Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(5)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0



Paul