I want to create a macro to remove all the rows of a table in which the cell
in column C contain "FM" within the text string. For some reason, when I set
up a macro to do a filter of "contains FM" then try to delete just those
rows, the macro deletes all rows.

After that ... I need to do the opposite. Need to delete all rows that does
NOT contain "FM".

Any help is appreciated.

Hi,

Right click your sheet tab, view code and paste this in and run it and all
rows with FM in the string in Col C will be deleted.

If you then to go on the delete all rows that don't contain FM all yoiur
data will be gone so I think you need to re-visit the second part of your
question

Sub delete_Me()
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & Lastrow)
For Each c In MyRange
If InStr(c, "FM") Then 'Red change to suit
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike


"kevin" wrote:

I want to create a macro to remove all the rows of a table in which the cell
in column C contain "FM" within the text string. For some reason, when I set
up a macro to do a filter of "contains FM" then try to delete just those
rows, the macro deletes all rows.

After that ... I need to do the opposite. Need to delete all rows that does
NOT contain "FM".

Any help is appreciated.

Worked great. I have two people working off this table. One of them works
with the ones containing "FM". The other works on those items not containing
"FM". My intent was to set up different macros for each to seperate the
lists out for them.

What changes would I make to delete those not containing "FM"?

Thanks again!

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it and all
rows with FM in the string in Col C will be deleted.

If you then to go on the delete all rows that don't contain FM all yoiur
data will be gone so I think you need to re-visit the second part of your
question

Sub delete_Me()
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & Lastrow)
For Each c In MyRange
If InStr(c, "FM") Then 'Red change to suit
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike


"kevin" wrote:

I want to create a macro to remove all the rows of a table in which the cell
in column C contain "FM" within the text string. For some reason, when I set
up a macro to do a filter of "contains FM" then try to delete just those
rows, the macro deletes all rows.

After that ... I need to do the opposite. Need to delete all rows that does
NOT contain "FM".

Any help is appreciated.

Kevin,

change this line

If InStr(c, "FM") Then

to This

If InStr(c, "FM") < 1 Then

Mike

"kevin" wrote:

Worked great. I have two people working off this table. One of them works
with the ones containing "FM". The other works on those items not containing
"FM". My intent was to set up different macros for each to seperate the
lists out for them.

What changes would I make to delete those not containing "FM"?

Thanks again!

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it and all
rows with FM in the string in Col C will be deleted.

If you then to go on the delete all rows that don't contain FM all yoiur
data will be gone so I think you need to re-visit the second part of your
question

Sub delete_Me()
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & Lastrow)
For Each c In MyRange
If InStr(c, "FM") Then 'Red change to suit
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike


"kevin" wrote:

I want to create a macro to remove all the rows of a table in which the cell
in column C contain "FM" within the text string. For some reason, when I set
up a macro to do a filter of "contains FM" then try to delete just those
rows, the macro deletes all rows.

After that ... I need to do the opposite. Need to delete all rows that does
NOT contain "FM".

Any help is appreciated.

Thanks a lot ... this worked perfect.

"Mike H" wrote:

Kevin,

change this line

If InStr(c, "FM") Then

to This

If InStr(c, "FM") < 1 Then

Mike

"kevin" wrote:

Worked great. I have two people working off this table. One of them works
with the ones containing "FM". The other works on those items not containing
"FM". My intent was to set up different macros for each to seperate the
lists out for them.

What changes would I make to delete those not containing "FM"?

Thanks again!

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it and all
rows with FM in the string in Col C will be deleted.

If you then to go on the delete all rows that don't contain FM all yoiur
data will be gone so I think you need to re-visit the second part of your
question

Sub delete_Me()
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & Lastrow)
For Each c In MyRange
If InStr(c, "FM") Then 'Red change to suit
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike


"kevin" wrote:

I want to create a macro to remove all the rows of a table in which the cell
in column C contain "FM" within the text string. For some reason, when I set
up a macro to do a filter of "contains FM" then try to delete just those
rows, the macro deletes all rows.

After that ... I need to do the opposite. Need to delete all rows that does
NOT contain "FM".

Any help is appreciated.