Delete Row

**drums4monty** · 11-17-2006, 12:09 PM

Hi

I want to check a worksheet where column C has figures entered. How do I check this column and if it is blank or has a 0 (zero) entered in it, the row is deleted. The range is A9:E119 for the whole work sheet and the Column range is C9:C119.

I have tried to write it mylef but I just dont know enough yet.

**Simon Lloyd** · 11-17-2006, 01:19 PM

Hi just add a module and paste this in, you can either run it via a command button or give it a keyboard shortcut

Sub removeblank()
Dim Rng As Range
Dim MyCell
Dim i
i = 1
Set Rng = Range("C9:C119")
For Each MyCell In Rng
If MyCell = Null Or MyCell = 0 Then
MyCell.EntireRow.Delete
End If
i = i + 1
Next
MsgBox "There were " & i & " Rows Deleted!"
End Sub

Regards,
Simon

**drums4monty** · 11-17-2006, 01:32 PM

Hi Simon

Many thanks for the help, when I run the code I get this error:

Delete method of Range class failed

and when I press debug it highlights this line:

MyCell.EntireRow.Delete

Regards

Alan

**Raiby** · 11-17-2006, 01:35 PM

Hi Alan

And what do you think of this?

Sub Delete()
For i = 9 To 119
For j = 3 To 3
If Cells(i, j) = "0" Or Cells(i, j) = "" Then
Rows(i).Delete
End If
Next j
Next i
End Sub