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

**johnny_tc** · 08-23-2012, 04:51 AM

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

**HaHoBe** · 08-23-2012, 06:20 AM

Hi, johnny_tc,

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

TIA
Holger

**johnny_tc** · 08-23-2012, 07:02 AM

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

**HaHoBe** · 08-23-2012, 07:14 AM

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

**johnny_tc** · 08-23-2012, 07:20 AM

Wonderful!!!
Thank you once again..!!!

**mazrientes** · 10-25-2012, 07:35 AM

Hi,
I Just used your code to delete some rows from a specified column, however if i wanted to use a wildcard search in a particular column for example (from above) find any cell that contains the word "country" then delete that row. How would you that do that please?

**HaHoBe** · 10-25-2012, 01:57 PM

Hi, mazrientes,

welcome to ExcelForum. Please note that according to Rule #2 you should start your own thread with your question.

I´d rely on the Autofilter for this kind of problem (use "contains" for the item you search for) instead of a loop with wildcards. And if you want a loop alter the parameter from lookat:=xlWhole to lookat:=xlPart.

Ciao,
Holger

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions