Locate column with specific title and delete rows if predefined set of text is found

Hi Guys,

I am trying to develop a macro that will search for a particular column heading (say Country) among many others, and remove rows under it, if it finds specific text (say Angola, Greece, Poland, etc) under the Country column.
So far the macros that i saw deals with deleting one criteria, not many.

I have just started exploring VBA, so a simple code with adequate explanation on how it works will be of great help.

Regards.

Hi, johnny_tc,

please change the name of the worksheet as desired and try this macro on a copy of your workbook:

Sub DeleteCountry()

Dim lngCounter As Long
Dim lngLastRow As Long
Dim rngFound As Range
Const cstrSEARCH As String = "Country"

With Sheets("Data")
  Set rngFound = .Rows("1:1").Find(what:=cstrSEARCH, _
      LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
  If Not rngFound Is Nothing Then
    lngLastRow = .Cells(Rows.Count, rngFound.Column).End(xlUp).Row
    For lngCounter = lngLastRow To 2 Step -1
      Select Case UCase(.Cells(lngCounter, rngFound.Column).Value)
        Case "GREECE", "ANGOLA", "POLAND"
          Rows(lngCounter).Delete
        Case Else
          'keep record
      End Select
    Next lngCounter
  End If
End With

Set rngFound = Nothing

End Sub

Macro will search the string "Country" in row 1 on that sheet. If no match is found no further action (we could implement a MsgBox for information). It the checks the last entry in that column and starts working from the bottom up by checking if the contents (which I changed to UpperCase) will match. If they do row will be deleted.

Ciao,
Holger

Wow!!! That works like a charm.
Thank you very much for the quick reply.

Hi, johnny_tc,

you´re welcome. If your question has been answered please mark the thread as solved (Thread Tools opening post).

TIA
Holger

A small issue came up. I just modified the Case terms to find text of my choice.
Now when i am running the macro, it is showing "Compile error: Invalid or unqualified reference"
Any idea what the problem could be?Below is how i edited your code:

Sub DeletePublicationType()

Dim lngCounter As Long
Dim lngLastRow As Long
Dim rngFound As Range
Const cstrSEARCH As String = "Publication Type"

Sheets("Raw Data").Select
  Set rngFound = .Rows("1:1").Find(what:=cstrSEARCH, _
      LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
  If Not rngFound Is Nothing Then
    lngLastRow = .Cells(Rows.Count, rngFound.Column).End(xlUp).Row
    For lngCounter = lngLastRow To 2 Step -1
      Select Case (.Cells(lngCounter, rngFound.Column).Value)
        Case "Country Business Guide", "Country HR Web Guide", "Country Guide", "Country Snapshot", "Business Guide"
          Rows(lngCounter).Delete
        Case Else
          'keep record
      End Select
    Next lngCounter
  End If
End With

Set rngFound = Nothing

End Sub