What code to change to delete rows?

bdunk

Active Member
Joined
Aug 1, 2002
Messages
290
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?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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.

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

bdunk

Active Member
Joined
Aug 1, 2002
Messages
290
Thanks

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

Bdunk
 

bdunk

Active Member
Joined
Aug 1, 2002
Messages
290
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Hi!

Try adding this line before the .specialcells(4)...statement:
Code:
.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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,229
Messages
5,571,019
Members
412,354
Latest member
Stj99
Top