Deleting Row question

[markb9]

Hi, I am having a problem with deleting row data in excel. I want the macro to look at columns H and I, starting from row 17 and going down to row 500. I want the macro to delete the entire row if the both the values in H and I for that row are blank or 0.

I'v been trying to edit the following code to work but it does not start from row 17 and moves all the rows in the table up to row 1:

Sub IfBothBlankDeleteRow()
BC1 = UCase("H")
BC2 = UCase("I")
TopRow = 17
BottomRow = 53

Application.ScreenUpdating = False
With Range(BC1 & TopRow & ":" & BC2 & BottomRow)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="="
.AutoFilter Field:=Asc(BC2) - Asc(BC1) + 1, Criteria1:="="
Columns(BC1).SpecialCells(4).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Thanks for the help

 

[Andrew Poulsom]

Try replacing:

Columns(BC1).SpecialCells(4).EntireRow.Delete

with:

.SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[markb9]

Thats solved the problem for the blank cells, thanks.

Is there a way to edit what i've already done, with your help, to eliminate cells occupied with a 'blank', '0' or '-' ?

Also is there a way to get rid of the auto filter that i have placed in there without affecting the way the macro is working?

Thanks again

 

[Andrew Poulsom]

You can only have one criteria for each field at a time, so you would need to repeat the autofilter/delete for the other two.

 

[markb9]

Thats great, thanks