How to Delete Rows Based on Conditions?

H

hce

Dear All

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

I only need those that are "Unused" and >0 which in this case, th
macro should delete all rows except for No.2 because it is unused an
0 (24). Can you help me with the code please...? I was not able t
Click to expand...
write a code that would work...

cheer
 
B

Bob Phillips

Do an autofilter on those 2 conditions, and delete the visible rows.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

hce

Hi

I am aware of the filter function... However, I always have to do this
and it's a chore... Besides, I need vba code because the data structure
is not as "nice" as I have presented here... it's not possible for me
to do filter with the structure I have and this code here which I need
is actually part of a bigger macro.... anyway i really appreciate your
suggestions...

cheers
 
R

Ron de Bruin

Try this

I use column B and C in this example
It will search in row 2 -100

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 100
For Lrow = EndRow To StartRow Step -1

If .Cells(Lrow, "B").Value = "Unused" And _
.Cells(Lrow, "C").Value > 0 Then
'do nothing
Else
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
 
Top