custom autofilter problem

Jshugs

New Member
Joined
Mar 24, 2009
Messages
23
Hello-

I'm trying to create a macro that uses a form where someone can search for a part number (or part of a part number) in a field and the macro will search for that part. I'm using the custom autofilter "contains" option to do this.

Code:
 Sheets(MainSheet).Range("$B$1:$O$" & DataLastRow).AutoFilter Field:=Col - 1, Criteria1:= _
        SearchWords(jj), Operator:=xlFilterValues

If Sheets(MainSheet).Range(Cells(2, Col), Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible).Count > 0 Then

On Error Resume Next

 Set FiltRange = Sheets(MainSheet).Range(Cells(2, Col), Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible)

On Error GoTo ERR1
        
End If
It works fine, except when someone types in something that returns no results, like SearchWords(jj)="*adafsfd*" I've tried everything to fix it. The line below gives the error

Code:
Sheets(MainSheet).Range(Cells(2, Col), Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible)
I tried catching that error, but I still get a runtime "No cells were found" Does anyone know how I can fix this? Maybe check if no cells are visible or something?


THANKS IN ADVANCE!

-Jon
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jshugs

New Member
Joined
Mar 24, 2009
Messages
23
I think i made a little mistake. It is actually a "text filter", not a "custom filter", but I don't think it matters too much.


thanks!
-Jon
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I usually put in a count after applied the filter to see if there are more than 1 rows.
 

Jshugs

New Member
Joined
Mar 24, 2009
Messages
23
how do you count? I thought I tried that, and it still comes up with an error. Refer to my code above.

thanks!
-Jon
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
Jon

What's a 'text filter'?

What you seem to be using is an Auto Filter and you don't seem to be qualifying all your ranges. ie not providing a worksheet reference.

This is your code with the ranges properly referenced.
Code:
    With Sheets(MainSheet)

        .Range("$B$1:$O$" & DataLastRow).AutoFilter Field:=Col - 1, Criteria1:=SearchWords(jj), Operator:=xlFilterValues
 
        If .Range(.Cells(2, Col), .Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible).Count > 0 Then
 
            Set FiltRange = .Range(.Cells(2, Col), .Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible)
 
        End If

    End With
It probably won't solve the problem you describe but it's always a good idea.

What error message are you getting?

Is it something to do with there being no visible cells?

If it is then perhaps you could add a check to see if the filter is going to return any records before you go ahead with it.

One way to do that might be to use Application.WorksheetFunction.CountIf.
 

Jshugs

New Member
Joined
Mar 24, 2009
Messages
23
Looks like Application.WorksheetFunction.CountIf worked!!! Thank you!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,862
Messages
5,766,809
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top