Deleting rows when certain cells are blank
 

Hi,

I am using this code to delete the row if the cell in column A is blank.
Dim i, j As Integer
Set starta = ActiveSheet.Range("a2")
LR = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = LR To 0 Step -1
If starta.Offset(i, 0).Value = 0 Then starta.Offset(i, 0).EntireRow.Delete
Next i

I would then like to delete a row if the cells in columns B, C, D, and E are
all blank even if the cell in column A is not blank

Any ideas?

Deleting rows when certain cells are blank
 

Matt,

Set starta = ActiveSheet.Range("a2")
LR = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = LR To 0 Step -1
If starta.Offset(i, 0).Value = 0 Or _
Application.CountA(starta.Offset(i, 1).Resize(1, 4)) = 0 _
Then starta.Offset(i, 0).EntireRow.Delete
Next i

HTH,
Bernie
MS Excel MVP


"Matt G" wrote in message
...
Hi,

I am using this code to delete the row if the cell in column A is blank.
Dim i, j As Integer
Set starta = ActiveSheet.Range("a2")
LR = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = LR To 0 Step -1
If starta.Offset(i, 0).Value = 0 Then starta.Offset(i, 0).EntireRow.Delete
Next i

I would then like to delete a row if the cells in columns B, C, D, and E are
all blank even if the cell in column A is not blank

Any ideas?

Deleting rows when certain cells are blank
 

Give this macro a try...

Sub DeleteRowsIfBtoEareBlank()
Dim LR As Long, i As Long, R As Range
On Error Resume Next
With ActiveSheet
LR = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For i = LR To 1 Step -1
Err.Clear
Set R = .Range("B" & i & ":E" & i).SpecialCells(xlCellTypeBlanks)
If Err.Number = 0 Then
If R.Count = 4 Then .Rows(i).Delete
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"Matt G" wrote in message
...
Hi,

I am using this code to delete the row if the cell in column A is blank.
Dim i, j As Integer
Set starta = ActiveSheet.Range("a2")
LR = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = LR To 0 Step -1
If starta.Offset(i, 0).Value = 0 Then starta.Offset(i,
0).EntireRow.Delete
Next i

I would then like to delete a row if the cells in columns B, C, D, and E
are
all blank even if the cell in column A is not blank

Any ideas?