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

Hi, johnny_tc,

I used an With...End With and referred to the sheet within the (no need for the sheet to be activates).

You select the sheet so any information on Rows, Columns, cells, Ranges is lost. That´s the reason for your run time error.

If you want to go on with

Sheets("Raw Data").Select

just add the line directly beneath that line of Code

With ActiveSheet

and leave everything else as it was.

Or you have a go with

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

Set rngFound = Nothing

End Sub

Ciao,
Holger