Deleting a row with a keyword 4

[Mizzness]

Hi All.

I want to have a macro search a worksheet and delete said rows everytime a keyword is found in that column.
Example: delete all rows when the keyword "swap" is found.
The keyword is constant & will only be found in column "C" in worksheet "PV".

I have attached my code for review and modification.

Thanks in advance for any & all help.

Sub Test()
Delete1Row ("swap&quot
End Sub

Sub Delete1Row(swap)
Const COL_TEST = 1 ' Column "C"
Dim nRow As Long
Dim nLastRow As Long
Dim sRowsToDelete As String
With ActiveSheet.UsedRange
nLastRow = .Rows.Count + .Row - 1
End With
For nRow = 1 To nLastRow
If Cells(nRow, COL_TEST) = swap Then
sRowsToDelete = nRow & ":" & (nRow + 0)
Range(sRowsToDelete).EntireRow.Delete
End If
Next nRow
End Sub

 

[TonyJollans]

Hi Mizzness,

You can use a filter to do this ..

With Sheets("PV").Columns("C:C")
    .AutoFilter Field:=1, Criteria1:="swap"
    .EntireRow.Delete Shift:=xlUp
End With

Enjoy,
Tony

 

[Mizzness]

Tony,

Thanks for the quick reply.
I pasted in your code however all this does is delete the first row.
Have I gone astray ?

Thanks.

 

[Mizzness]

All.

Maybe I need to clarify what i want from this macro.
I wish to have all rows deleted in column "C" when their cell contains the keyword "swap".
I'll be attaching a button to this for ease of use.

Thanks in advance.
Mizzness

 

[TonyJollans]

Hi Mizzness,

If you just pasted it into a module by itself, I'm not sure what to suggest - it should work and it works for me.

It won't produce exactly the same code but try recording yourself setting an autofilter on the column, filtering for "swap" and deleting all the (visible) rows.

Enjoy,
Tony

 

[Mizzness]

Tony,

My error.
The keyword "swap" is part of other alphanumeric data in the cell.
Your filter idea is great if that was the only keyword.

Thanks
Mizzness

 

[TonyJollans]

Hi Mizzness,

Will changing the filter criteria to a pattern help?

Criteria1:="

*

swap

*

"

Enjoy,
Tony

 

[Gavona]

Autofilter can also use the criteria 'contains'. Try

Criteria1:="=*swap*"

within the code you already have

 

[DaleWatson123321]

Hi Mizzness,

I've created a working model (with macro button) that does exactly what you've described.

Also, if you're interested in other "Advanced Filter" examples, I have a "collection" I'm prepared to share. They are a variety of files I've created over the past couple of years for Tek-Tips members.

If you email me, I'll send the file via return email.

If anyone else is interested, feel free to ask - but please be specific as to what file(s) you're asking for. ;-)

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Gavona]

Presumably Dale your solution advanced-filters the data to be retained to a new location?

The only limitation of the approach is that the extracted data contains only values not formulae - or have you found a way around this?

A copy of your example sheet would be most welcome as I know I will be able to learn from it.

 

[Mizzness]

Tony & Gavona,

Thank you for helping me in the difficulty of simplicity.

Dale,
Thanks for sharing your files.

One issue I still have is that the top row is deleted as all data is shifted up one row.
Can this be eliminated ?
I have already deleted the Shift:=xlUp portion of Tony's code posted earlier.

Thanks again.

 

[Mizzness]

All.

The final code is as follows with a modification to save the first 2 rows as they contain headers.

Sub Filter()
'
With Sheets("PV&quot.Columns("C:C&quot.Rows("3:3000&quot
.AutoFilter Field:=1, Criteria1:="=*libor*"
.EntireRow.Delete Shift:=xlDown
.AutoFilter Field:=1, Criteria1:="=*swap*"
.EntireRow.Delete Shift:=xlUp
End With
End Sub

Thanks again for your help. A star has been posted for you in appeciation.

Regards,
Mizzness