What code to change to delete rows?

[bdunk]

I am using the following code to delete rows that have a "0" or nothing in the Column A cell value.

Sub Test1()
With Columns(1)
.Replace What:="0", Replacement:="", LookAt:=xlWhole
.SpecialCells(4).EntireRow.Delete
End With
End Sub

How would I modify this code to look in column C for cells that have a "1"? If it does then delete the whole row.

Thanks for the help.

PS what does the .SpecialCells(4).EntireRow.Delete line of code do?

 

[Von Pookie]

SpecialCells(4) is telling the code to delete the entire row for each cell that is blank within column A.

It can also be referenced as SpecialCells(xlCellTypeBlanks).

Assuming there are no blanks in column C that you want to keep, you could simply use the same code with a minor change.

Sub Test1()
With Columns(3) '3 =column C
.Replace What:="1", Replacement:="", LookAt:=xlWhole
.SpecialCells(4).EntireRow.Delete
End With
End Sub

 

[bdunk]

Thanks

Thanks Kristy works great. I just wasn't patient enough for it to complete its job.

Bdunk

 

[bdunk]

One more question

How would I add looking for value "0" also in this code. So I want it to look for 1 or 0 in Column C. I have it working for 1 or nothing I just want it to look for "0" as well.

Thanks

Brian

 

[Von Pookie]

Hi!

Try adding this line before the .specialcells(4)...statement:

.Replace What:="0", Replacement:="", LookAt:=xlWhole

This way it should replace all 1's with blanks, then all of the 0's. Then it deletes the blank cells.