How to Delete Rows based on conditions?

H

hce

Dear All

Happy Mothers' Day!!!


I would like to ask how to write vb code to delete rows based o
conditions..

I have 4 columns of data and I would like to delete all data in colum
B if they are not specified "Unused" and I also want to delete al
those in Column C that are "0" in value.

In a nutshell, I only want data that are "Unused" and not equal to 0
Could someone kindly show me how to do it...?

Cheer
 
D

Don Guillett

The trick is to work from the bottom up. Here is one that deletes rows with
a date.

Sub deltetedate1()
x = Cells(Rows.Count, "b").End(xlUp).Row
For i = x To 1 Step -1
If IsDate(Cells(i, "b")) Then Cells(i, "b").Rows.Delete
Next
End Sub

so you could modify to
Sub deltetestuff()
x = Cells(Rows.Count, "a").End(xlUp).Row
For i = x To 1 Step -1
if cells(i,"b")="Unused or cells(i,"c")=0 then
cells(i,"a").entirerow.delete
Next
End Sub
 
D

Don Guillett

word wrap so

On the second on, either move the
cells(i,"a").entirerow.delete
line up to follow the then or use an
end if
as the next line
--
Don Guillett
SalesAid Software
[email protected]
Don Guillett said:
The trick is to work from the bottom up. Here is one that deletes rows with
a date.

Sub deltetedate1()
x = Cells(Rows.Count, "b").End(xlUp).Row
For i = x To 1 Step -1
If IsDate(Cells(i, "b")) Then Cells(i, "b").Rows.Delete
Next
End Sub

so you could modify to
Sub deltetestuff()
x = Cells(Rows.Count, "a").End(xlUp).Row
For i = x To 1 Step -1
if cells(i,"b")="Unused or cells(i,"c")=0 then
cells(i,"a").entirerow.delete
Next
End Sub
Click to expand...
 
G

Gord Dibben

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value <> "unused" Or _
Cells(RowNdx, "A").Value = 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Gord Dibben Excel MVP
 
H

hce

Dear All

Thank you all for your help...

Hi Gord

I tried your suggestion... the macro deleted everything in m
worksheet... I just copied the code and did no changes... Am I suppose
to change anything...?

My data's supposed to look like this...

No|Status|Count
1|Used|12
2|Unused|24
3|Available|23
4|Unused|0
5|Used|45

Hence, I only need those that are "Unused" and >0 which in this case
the macro should delete all rows except for No.2 because it is unuse
and >0 (24). Can you help me please...?

cheer
 
Top