Excel crashing when no visible rows / no results found when using autofilters

[Tom A]

Hi,

I'm running different auto filters on my worksheet to search for specific text, one after the other. I first filter by date and then by keyword.

Excel crashes when there are no filtered rows / when it can't find the keyword in the worksheet.

Excel crashed so I didn't manage to save the exact code I was using but I've tried checking for the number of visible rows by using:
.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 = 0

These are the filters that Excel is crashing on usually:

'Filter Data
If Criteria2 = "" Then
DataRange.AutoFilter Field:=AutoFilterField, Criteria1:="=" & Criteria1, Operator:=xlAnd
Else
'if filtering using 2 criteria then check if logical operator is and or or.
If Criteria2 <> "" And LogicalOperator = "xlAnd" Then
DataRange.AutoFilter Field:=AutoFilterField, Criteria1:="=" & Criteria1, Operator:=xlAnd, Criteria2:=Criteria2
ElseIf Criteria2 <> "" And LogicalOperator = "xlOr" Then
DataRange.AutoFilter Field:=AutoFilterField, Criteria1:="=" & Criteria1, Operator:=xlOr, Criteria2:=Criteria2
  End If
  End If

thanks,
Tom

 

[Hui]

Tom

Can you share the file or a sample file ?

 

[Hui]

Could you also try removing the red section as shown below from the first filter only

If Criteria2 = "" Then
DataRange.AutoFilter Field:=AutoFilterField, Criteria1:="=" & Criteria1, Operator:=xlAnd
Else

 

[Tom A]

Thanks for your help Hui. it seems to be working now. I removed the first if statement and added it at the end of the second one instead.

I will test it for a few days and see if it crashes again.

Tom

Can you share the file or a sample file ?

 

[Tom A]

Excel 2013 is still crashing sometimes when filtering and no results are found.
When it is working fine then it can filter in less than 2 seconds but when it crashes then it can take 5 minutes for it to react. I think there may be a memory leak occurring somehow as I got a out of memory error message when using VBA the other day also.

I'm not sure how I can share my workbook as all my worksheets are closely interlinked and have private data.

Could you also try removing the red section as shown below from the first filter only

If Criteria2 = "" Then
DataRange.AutoFilter Field:=AutoFilterField, Criteria1:="=" & Criteria1, Operator:=xlAnd
Else