Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Deleting a row with a keyword 4

Status
Not open for further replies.

Mizzness

Programmer
May 2, 2003
174
US
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")
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
 
Hi Mizzness,

You can use a filter to do this ..

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

Enjoy,
Tony
 
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.
 
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
 
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
 
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
 
Hi Mizzness,

Will changing the filter criteria to a pattern help?

Code:
Criteria1:="
Code:
*
Code:
swap
Code:
*
Code:
"

Enjoy,
Tony
 
Autofilter can also use the criteria 'contains'. Try

Criteria1:="=*swap*"

within the code you already have
 
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
 
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.
 
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.
 
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").Columns("C:C").Rows("3:3000")
.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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top