Excel - Deleting rows that do not contain certain strings 2

[johngiggs]

I was wondering if it's possible to create a formula/module to delete all rows that do not contain any of the following strings: DISS, JTE, or GMS. These strings are not case sensitive, so they can be either case. Any help would be greatly appreciated.

Thanks,

John

 

[RamziSaab]

hey,

This can be easily done with a bit more information...are these strings in a certain column... or can they be in any column...also are they by themselves in the cell or is there other things in the cell

 

[johngiggs]

Ramzi,

They can be in any column and can be located anywhere withing the cell.

Thanks,

John

 

[RamziSaab]

ok a bit more complicated then i thouhg
How many columns are we talking about here?

I could probably come up with something but it might take some time!

 

[johngiggs]

Ramzi,

There are about 100 columns.

Thanks,

John

 

[RamziSaab]

hey this is a rouhg code that should do what u want

Sub test()
With ThisWorkbook.Worksheets("Sheet1&quot

i = .UsedRange.Rows.Count
For h = 1 To i
On Error Resume Next
strrow = Cells.Find("DISS&quot.Address
If strrow = "" Then
Else
strdelete = Range(strrow).Row
'MsgBox strdelete
Rows(1).EntireRow.Delete
End If


strrow = Cells.Find("JTE&quot.Address
If strrow = "" Then
strdelete = Range(strrow).Row
'MsgBox strdelete
Rows(1).EntireRow.Delete
End If


If strrow = "" Then
strrow = Cells.Find("GMS&quot.Address
strdelete = Range(strrow).Row
'MsgBox strdelete
Rows(1).EntireRow.Delete
End If
Next h


End With
End Sub

just put it in a module and run it...also i would make sure u have a saved backup before u run this tell me if u need more help

 

[RamziSaab]

oops just reread ur message i am deleting the ones that contrain those strings u want it the other way around
will change it ...

 

[DaleWatson123321]

Hi John,

I've been working on a model that uses Excel's Advanced Filter. It's now complete, and it does what you require.

I've set up some additional examples that will demonstrate some of the capability of the Advanced Filter. These examples are attached to macro-buttons labeled:
"Extract Data", "Filter-In-Place", "Delete Hidden Data", "Show All Data", and "Delete Visible Data".

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

As always, if anyone else is interested in a copy of the file, please feel free to ask.

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

 

[yeong1015]

Hi DaleWatson123321, I would like to have this file of yours. My address : yeongwh@yahoo.com

Thanks

 

[PeterMoran]

Hi RamziSaab,

Great Effort! better than I could do!

Can I just make a couple of suggestions:

1. May I suggest it is better to use the more generic "ActiveSheet" rather than "Sheet1" if you are giving code because it will run anywhere while Sheet1 will only run on a sheet with a specific name.

2. In this situation it might have been better to provide a simpler macro giving John the ability to enter whatever string he wanted, and then taking appropriate action, rather than including in code all his current specific needs. Tomorrow he may want to delete with a different string and it is easier if he is able to just run the macro and enter whatever string he requires, rather than having to amend a macro you have given him, which he may not fully understand.

Keep up the enthusiasm!

Peter Moran

 

[johngiggs]

Dale,

Can you please send me a copy of the file?

johngiggs@yahoo.com

Thanks,

John

 

[KenWright]

Not sure how fast it will run - depends on how much data. Dale's code may well be a lot faster if using a filter:-

Sub DelRowsNotCont()
Dim x As Long
Dim c As Range


Application.ScreenUpdating = False

For x = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
With ActiveSheet.UsedRange.Rows(x)
Set c = .Find("abc", LookIn:=xlValues)
If c Is Nothing Then
Set c = .Find("def", LookIn:=xlValues)
If c Is Nothing Then
Set c = .Find("ghi", LookIn:=xlValues)
If c Is Nothing Then

.EntireRow.Delete
End If
End If
End If
End With
Next x

Application.ScreenUpdating = True

End Sub

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[johngiggs]

Thanks a lot Ken!!! That works great!! I was able to get all of the data I needed.

Thanks,

John