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?

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?

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?

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?