# Macro to delete a row if a range of cells is blank

#### John T

##### Board Regular
I need a macro to delete a row if there is no data in cells A5:I104

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Do you mean, deleting for example row 5, if there's no data from column A to column I and the same for row 6 up till 104?

Which row do you want to delete ??

Which row do you want to delete ??

I want to delete all the rows which contain no data in cells A5:I104.

so if for example rows 80 and 90 hand no data in columns A:I it would delete the entire row.

Something like
Code:
``````Sub delrows()
Dim wf As WorksheetFunction

Set wf = Application.WorksheetFunction
For I = 104 To 5 Step -1
If wf.CountBlank(Range("A" & I & ":" & "R" & I)) = Range("A" & I & ":" & "R" & I).Count Then
Rows(I).EntireRow.Delete
End If
Next I

End Sub``````

Something like
Code:
``````Sub delrows()
Dim wf As WorksheetFunction

Set wf = Application.WorksheetFunction
For I = 104 To 5 Step -1
If wf.CountBlank(Range("A" & I & ":" & "R" & I)) = Range("A" & I & ":" & "R" & I).Count Then
Rows(I).EntireRow.Delete
End If
Next I

End Sub``````

This doesn't seem to do anything on my worksheet.

This doesn't seem to do anything on my worksheet.
A to I you said, i accidentally used A to R,but in any case it works for me. Please try the modified version below

Code:
``````Sub delrows()
Dim wf As WorksheetFunction

Set wf = Application.WorksheetFunction
For I = 104 To 5 Step -1
If wf.CountBlank(Range("A" & I & ":" & "I" & I)) = Range("A" & I & ":" & "I" & I).Count Then
Rows(I).EntireRow.Delete
End If
Next I
End Sub``````

A to I you said, i accidentally used A to R,but in any case it works for me. Please try the modified version below

Code:
``````Sub delrows()
Dim wf As WorksheetFunction

Set wf = Application.WorksheetFunction
For I = 104 To 5 Step -1
If wf.CountBlank(Range("A" & I & ":" & "I" & I)) = Range("A" & I & ":" & "I" & I).Count Then
Rows(I).EntireRow.Delete
End If
Next I
End Sub``````

Thanks that seems to work correctly.

Thanks that seems to work correctly.
Seems to or Works thanks for the feedback

hi.

deletes a row if all cells in the same row are blank (Cols A-I)
Code:
``````Sub del_r()
Dim i As Long

For i = 104 To 5 Step -1
If Application.CountA(Range("A" & i & ":" & "I" & i)) = 0 Then Rows(i).Delete
Next i
End Sub``````

deletes a row if at least one cells in the same row is blank (Cols A-I)
Code:
``````Sub del_r2()
Range("A5:I104").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub``````

Replies
0
Views
112
Replies
12
Views
362
Replies
12
Views
646
Replies
6
Views
123
Replies
2
Views
159

1,203,501
Messages
6,055,766
Members
444,821
Latest member
Shameer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back