Keep only rows which contain a specific text, rest delete

[mickeystanford_alumni]

Hi everyone,
Thank you in advance for your help.

I have received a very messy excel where I want to do a clean of data. Basically, I need to tell excel to find in "column G" of the excel the words "Asia", "Europe", "USA" and keep the whole rows that contain those words. Preferably in order. So in an excel of 1000 rows, if with our filter we have 100, to have those from row 1-100. Then the rest delete it.

Thank you so much.
Appreciate the help and support.

 

[Fluff]

How about

Sub mickey()
   Dim Ary As Variant
   
   With Sheets("Test")
      With .Range("G2", .Range("G" & Rows.Count).End(xlUp))
         ary = .Worksheet.Evaluate(Replace("unique(filter(@,(@<>""Asia"")*(@<>""Europe"")*(@<>""Usa"")))", "@", .Address))
      End With
      .Range("A1").AutoFilter 7, Application.Transpose(ary), xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

Change sheet name to suit

 

[mickeystanford_alumni]

HI @Fluff,

Thanks a lot for your response.
The code is not running correctly I believe, it forces me to manually run it every time. Is there any way to run it automatically so it goes through all rows?
In addition, when in "column G", there is no text, then the row is being kept, but I would like to delete that as well.

Many thanks one more time.

 

[Fluff]

This will delete the blanks as well

Sub mickey()
   Dim Ary As Variant
   
   With Sheets("Test")
      With .Range("G2", .Range("G" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate(Replace("unique(filter(@&"""",(@<>""Asia"")*(@<>""Europe"")*(@<>""Usa"")))", "@", .Address))
      End With
      .Range("A1").AutoFilter 7, Application.Transpose(Ary), xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

it forces me to manually run it every time

That's the whole idea of it.

 

[mickeystanford_alumni]

Thank you Fluff. It does work perfectly.

 

[mickeystanford_alumni]

This will delete the blanks as well

Sub mickey()
   Dim Ary As Variant
  
   With Sheets("Test")
      With .Range("G2", .Range("G" & Rows.Count).End(xlUp))
         Ary = .Worksheet.Evaluate(Replace("unique(filter(@&"""",(@<>""Asia"")*(@<>""Europe"")*(@<>""Usa"")))", "@", .Address))
      End With
      .Range("A1").AutoFilter 7, Application.Transpose(Ary), xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

That's the whole idea of it.

And if the idea were to run it automatically? Do you know how I could do it?

 

[mickeystanford_alumni]

Apologies for the several replies, but by doing manually my excel kind of crushes so won't be able to do it like that. If any suggestions for the automatic way, I would really appreciate so.

 

[Fluff]

How is the sheet being populated with data?

 

[mickeystanford_alumni]

Hi Fluff,
Just used this code (only for Asia actually), didn't need the other 2:

Last = Cells(Rows.Count, "G").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "G").Value) <> "ASIA" Then

Cells(i, "G").EntireRow.Delete
End If
Next i

I appreciate your help though. Thanks a lot.

 

[Fluff]

Glad you sorted it & thanks for the feedback.