How to Delete Rows based on conditions?

How to Delete Rows based on conditions?

Post by hce » Tue, 11 May 2004 00:20:30


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
 
 
 

How to Delete Rows based on conditions?

Post by Don Guille » Tue, 11 May 2004 01:08:52

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


--
Don Guillett
SalesAid Software
XXXX@XXXXX.COM

 
 
 

How to Delete Rows based on conditions?

Post by Don Guille » Tue, 11 May 2004 01:24:41

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
XXXX@XXXXX.COM


with
 
 
 

How to Delete Rows based on conditions?

Post by Gord Dibbe » Tue, 11 May 2004 01:28:08

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

On Sun, 9 May 2004 10:20:30 -0500, hce << XXXX@XXXXX.COM >>
 
 
 

How to Delete Rows based on conditions?

Post by hce » Tue, 11 May 2004 19:34:48

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
 
 
 

How to Delete Rows based on conditions?

Post by hce » Tue, 11 May 2004 23:17:42

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
write a code that would work...

cheer
 
 
 

How to Delete Rows based on conditions?

Post by Bob Philli » Tue, 11 May 2004 23:26:08

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)
 
 
 

How to Delete Rows based on conditions?

Post by hce » Tue, 11 May 2004 23:51:34

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


---
Message posted from http://www.yqcomputer.com/
 
 
 

How to Delete Rows based on conditions?

Post by Ron de Bru » Wed, 12 May 2004 01:51:15

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


--
Regards Ron de Bruin
http://www.yqcomputer.com/
 
 
 

How to Delete Rows based on conditions?

Post by hce » Wed, 12 May 2004 23:48:50

Hi Ron

Thank you so much!!! It worked perfectly!!!

Cheer