Filter records Not Like "Corp"

supmktg · Feb 21, 2006

I need to set up a filter for an excel mailing list to exclude names that contain "Company", "Corporation", etc. (or common abbreviations of same). In Access I can use Not like "*Corp*" and Not like "*Company*". I can't find a way to do this in Excel. Can anyone help?

Thanks,

Sup

HaHoBe · Feb 21, 2006

Hi. Sup,

without VBA use the advanced filter: go to a new sheet, copy the headings of the list to row 1 and enter your criteria like ="<>Cor*" one by one in each row. Start the advanced filter from the new sheet indicating the criteria, the new range where you want the data listed, take the option copy to another location and enter the original range of the data.

With VBA you could make good use of InStr or the namely Like Function...

Ciao,
Holger

supmktg · Feb 22, 2006

Holger,

="<>Cor*" doesn't work. I also tried <>"*Cor" and other variations of <> but none work using the advanced filter.

How would I build and use a vba function to filter out records?

Thanks,

Sup

HaHoBe · Feb 22, 2006

Hi, Sup,

I got it to work by using ="<>Cor*" (Excel2002 SP-3 running on Windows XP PRO SP-2).

Put code into a normal module and add further items if needed - please adjust the columns (this works for Column B on the active sheet):

Code:

Option Explicit

Sub SUP()
Dim lngCounter As Long
Dim lngLastRow As Long
Dim myArray As Variant
Dim bytCounter As Byte
myArray = Array("Corp", "Company")
lngLastRow = Range("B1").End(xlDown).Row
For lngCounter = lngLastRow To 2 Step -1
    For bytCounter = LBound(myArray) To UBound(myArray)
      If InStr(1, Cells(lngCounter, 2).Value, myArray(bytCounter), 1) > 0 Then
        Rows(lngCounter).EntireRow.Hidden = True
        bytCounter = UBound(myArray)
      End If
    Next bytCounter
Next lngCounter
End Sub

Ciao,
Holger

Rod D · Feb 22, 2006

Lose the quotes if you want to filter!

Hi Sup

The problem that you have when filtering is the quotes - strange as it may seem, the filter criteria should be...

<>* Corp*

I added a space after the first * so that the filter only looks at the beginning of the words, you can change this if you wish.

If you want to have more criteria, remember "OR" should be in seperate columns, "AND" should be on seperate rows. In your case, have your criteria set in multiple columns.

I have put in a screenshot that might help.

This works for me - Excel 2002
HTH
Rod

supmktg · Feb 22, 2006

Holger,

Thank you very much for your help, especially for the vba code example. Unfortunately, I can't get it to work for me. I've attached a workbook with my attempt. Could you tell me what I'm doing wrong?

Thanks,

Sup

Dave H · Feb 22, 2006

A simple formula may be easier =find("corp",b1) this formula will return a value if it finds the string and #value if not which then could be eliminated by =if(iserror(find("corp",b1)),0,1) this would return a zero if the string is not found and a 1 if the string was found.

HaHoBe · Feb 23, 2006

Hi, Sup,

your values to compare are listed in Column A while mine where in Column B. Please change one digit in the code to get it working:

Code:

      If InStr(1, Cells(lngCounter, [B][COLOR="Red"]1[/COLOR][/B]).Value, myArray(bytCounter), 1) > 0 Then

Ciao,
Holger

supmktg · Feb 23, 2006

Holger,

THANK YOU!!! That works perfectly!

How would I change the range to include the entire sheet?

Sup

HaHoBe · Feb 23, 2006

Hi, Sup,

the range for the loop is dynamic (as if you are in cell A1 and hit Ctrl + arrow down). It is limited by the first empty row in the column. If you want the whole column you could simply set the upper limit to Rows.Count (which is 2^16 at present) but then you sbould also make good use of Application.ScreenUpdating = False at the beginning of the code and ~ = True at the end. Maybe even include " " in the array

If there are values following empty rows in column A the best approach would be to use

Code:

Cells(Rows.Count, 1).End(xlUp).Row

and maybe also check for the value of the last cell in column A.

Ciao,
Holger

supmktg · Feb 24, 2006

Holger,

What little VBA I've done has been in Access not excel, so I'm not familiar with referencing columns and rows. It's possible, even likely that the key words might be in any of several columns in the spreadsheet. I've repeated the code like this:

Code:

myColumn = "B1"
mystart = 2
lngLastRow = Range(myColumn).End(xlDown).Row
For lngCounter = lngLastRow To 2 Step -1
    For bytCounter = LBound(myArray) To UBound(myArray)
      If InStr(1, Cells(lngCounter, mystart).Value, myArray(bytCounter), 1) > 0 Then
        Rows(lngCounter).EntireRow.Hidden = True
        bytCounter = UBound(myArray)
      End If
    Next bytCounter
Next lngCounter

myColumn = "C1"
mystart = 3

lngLastRow = Range(myColumn).End(xlDown).Row
For lngCounter = lngLastRow To 2 Step -1
    For bytCounter = LBound(myArray) To UBound(myArray)
      If InStr(1, Cells(lngCounter, mystart).Value, myArray(bytCounter), 1) > 0 Then
        Rows(lngCounter).EntireRow.Hidden = True
        bytCounter = UBound(myArray)
      End If
    Next bytCounter
Next lngCounter

myColumn = "D1"
mystart = 4

lngLastRow = Range(myColumn).End(xlDown).Row
For lngCounter = lngLastRow To 2 Step -1
    For bytCounter = LBound(myArray) To UBound(myArray)
      If InStr(1, Cells(lngCounter, mystart).Value, myArray(bytCounter), 1) > 0 Then
        Rows(lngCounter).EntireRow.Hidden = True
        bytCounter = UBound(myArray)
      End If
    Next bytCounter
Next lngCounter

etc. to cover the entire spreadsheet. I know there must be a more efficient way to reference all of the columns and rows, but I didn't follow your example.

Code:

Cells(Rows.Count, 1).End(xlUp).Row

it seemed to only reference Column A?

Also, can you tell me if there is a way to delete the rows instead of just hide them?

Thank you very much for your help!

Sup

HaHoBe · Feb 24, 2006

Hi, Sup,

you should make good use of a loop by using all the columns which are filled - somenthing that may be approached like

Code:

Sub UsingLoopAllFilledColumns()
Dim intColumn As Integer
Dim intCounter As Integer
Dim lngLastRow As Long
Dim lngCounter As Long
Dim myArray As Variant
Dim bytCounter As Byte

myArray = Array("ABC", "Corp")
intColumn = Range("1").End(xlToRight).Column

For intCounter = 2 To intColumn
  lngLastRow = Cells(1, intColumn).End(xlDown).Row
  For lngCounter = lngLastRow To 2 Step -1
    For bytCounter = LBound(myArray) To UBound(myArray)
      If InStr(1, Cells(lngCounter, intCounter).Value, myArray(bytCounter), 1) > 0 Then
        ' this will hide the row
        Rows(lngCounter).EntireRow.Hidden = True
'        ' this will delete the row
'        Rows(lngCounter).Delete
        bytCounter = UBound(myArray)
      End If
    Next bytCounter
  Next lngCounter
Next intCounter
End Sub

Frankly I didn´t test as I think the next macro does a better job. If the terms are all over the workbook maybe the following approach will turn out to be a little bit faster:

Code:

Option Explicit

Sub UseFind()
Dim lngCounter As Long
Dim lngLastRow As Long
Dim myArray As Variant
Dim bytCounter As Byte
myArray = Array("ABC", "Corp")
Dim strSearch As String
Dim rngCell As Range
Dim strCell As String

On Error GoTo err_handle

Application.ScreenUpdating = False
For lngCounter = LBound(myArray) To UBound(myArray)
  strSearch = myArray(lngCounter)
  Set rngCell = Cells.Find(strSearch)
  If Not rngCell Is Nothing Then
    strCell = rngCell.Address
    Do
      Rows(rngCell.Row).EntireRow.Hidden = True
      Set rngCell = Cells.FindNext(After:=rngCell)
    Loop While rngCell.Address <> strCell
  End If
  Set rngCell = Nothing
Next lngCounter

exit_here:
Application.ScreenUpdating = True
MsgBox "macro is done", vbInformation, "Ready"
Exit Sub

err_handle:
MsgBox "An error occurrred: " & vbCrLf & _
        "Errornummber: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description, vbInformation
Resume exit_here
End Sub

This macro doesn´t mess with loops over rows and columns...

Ciao,
Holger

shades · Feb 24, 2006

Just a note:

Contrary to "logic", it is better to DIM numbers as Long rather than Integer, because Excel converts all Integers to Long anyway.
________
NEVADA MARIJUANA DISPENSARIES

HaHoBe · Feb 24, 2006

Hi, shades,

so there should be no RTE in the following code according to your explanation as Excel shall convert Integers to Long (sorry if I got you wrong):

Code:

Dim intRowNumber As Integer
intRowNumber = 32786

I pretty much doubt that...

Ciao,
Holger

shades · Feb 26, 2006

No, but Excel treats it like a Long, even though it is Integer. In other words, the supposed advantages of using Integer to use less resources is not accurate.

There was an extended discussion about this very issue by some of the XL MVPs several months back. I will try to find that discussion. Here it is: Dick K's Blog.
________
Girlfriends pics

HaHoBe · Feb 27, 2006

Hi, shades,

it would be great if you could spot that disussion. But anyhow I´d never really cared at all for using less ressources - I´m pretty happy if my code is doing what I expect it to do...

Ciao,
Holger

shades · Mar 1, 2006

The link is in the last post (I had edited it).
________
Vaporgenie Vaporizer

HaHoBe · Mar 1, 2006

Hi, shades,

I think it´s time for me to get other or more focussing glasses - I didn´t realize that link (sorry for causing inconvenience). Thanks for it - I´ll have to think about that.

Ciao,
Holger

Filter records Not Like "*Corp*"

Registered User.

Locomotive Breath

Registered User.

Locomotive Breath

Attachments

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Locomotive Breath

Registered User.

Locomotive Breath

Registered User.

Locomotive Breath

Registered User.

Locomotive Breath

Registered User.

Locomotive Breath

Registered User.

Locomotive Breath

Similar threads

Users who are viewing this thread

Filter records Not Like "Corp"