PDA

View Full Version : For loop delete entire row if cell is blank



barim
01-20-2017, 02:21 PM
I don't understand why this code works::think:



On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


and this one doesn't work::think:



Sub RowDelete()
Dim lrow2 As Long
Dim c2 As Long

For Each c2 In ActiveSheet.Range("C2:C" & lrow2)
If IsEmpty(c2) = True Then
c2.EntireRow.Delete
End If
Next c2
End Sub


The purpose is to delete entire row if it encounters any blank cell in column C. Is there any way to do it with for loop, I just feel safer doing deletions with for loop. Thanks in advance.

SamT
01-20-2017, 04:48 PM
this one doesn't worklrow2 = 0, c2 is a number (long) also = 0

Sub RowDelete still won't work properly because it is deleting from top to bottom.

Look at this version

Sub RowDelete()
Dim lrow2 As Long
Dim rw As Long

With ActiveSheet
lrow2 = .Cells(Rows.Count, "C").End(xlUp).Row

For rw = lrow2 To 2 Step -1
If IsEmpty(.Cells(rw, "C")) Then .Rows(rw).Delete
Next i

End With
End Sub