deleting rows in a worksheet if condition is met
 

hi,

I am trying to come up with a code that will somehow delete an entire row if
it's content in a particular column meets the condition. Say I want to
delete the rows whose Status column says approved.

Anybody can help?

deleting rows in a worksheet if condition is met
 

You can do it manually by applying Autofilter to the status column and
selecting "approved" from the drop-down. Then highlight all the
visible rows and click on Edit | Delete Row. Then select "All" from
the filter drop-down.

Hope this helps.

Pete

On Oct 24, 5:23*pm, destine wrote:
hi,

I am trying to come up with a code that will somehow delete an entire row if
it's content in a particular column meets the condition. *Say I want to
delete the rows whose Status column says approved.

Anybody can help?

deleting rows in a worksheet if condition is met
 

Hi,

Right click your sheet tab, view code and paste this in. Change the letter
in the line
mycolumn = "C" 'Change to suit
to the column where your string is.

Sub copyit()
mycolumn = "C" 'Change to suit
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, mycolumn).End(xlUp).Row
Set MyRange = Range(mycolumn & "1:" & mycolumn & lastrow)
For Each c In MyRange
If UCase(c.Value) = "APPROVED" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub


Mike

"destine" wrote:

hi,

I am trying to come up with a code that will somehow delete an entire row if
it's content in a particular column meets the condition. Say I want to
delete the rows whose Status column says approved.

Anybody can help?

deleting rows in a worksheet if condition is met
 

I like this code and it works for some of my applications as well. I need
something similar that will delete the row if the column contains "FM" in a
text string (not equal to). I also need syntax that would delete the row if
it does NOT contain "FM".

Can that be done with your code here?

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Change the letter
in the line
mycolumn = "C" 'Change to suit
to the column where your string is.

Sub copyit()
mycolumn = "C" 'Change to suit
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, mycolumn).End(xlUp).Row
Set MyRange = Range(mycolumn & "1:" & mycolumn & lastrow)
For Each c In MyRange
If UCase(c.Value) = "APPROVED" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub


Mike

"destine" wrote:

hi,

I am trying to come up with a code that will somehow delete an entire row if
it's content in a particular column meets the condition. Say I want to
delete the rows whose Status column says approved.

Anybody can help?