VBA Advanced filter often crashes excel, what causes this? HELP please!

[logandiana]

I have a procedure that uses the advanced filter.

BOD is data on a sheet in another open workbook
crit and Plnt are different sheets in the macro workbook

Dim rgData, rgCriteria, rgOutput As Range

Set rgData = BOD.Range("A1").CurrentRegion   
Set rgCriteria = crit.Range("A1").CurrentRegion
Set rgOutput = Plnt.Range("B" & LR1 + 2).CurrentRegion

rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput    '<<<< this line seems to be the issue

This code DOES work. It runs fast and without issue about 90% of the time.
The other 10% of the time it crashes Excel, no spinning, no error messages, no warning. All Excel just disappears.
This is the same whether running the code or stepping through in debug.
The the criteria never changes. The only data that sometimes changes is the data that's on BOD.
But I have tested it repeatedly with the same data and I always hold my breath when I hit the button.


I can't figure out what causes it though. Is there a different way to write it so that it works 100% of the time?
Is it a computer issue? I have 16gb of RAM and an 8 core i7.
I have other things declared at the top of the module.

Dim OWI, BO, TempWB, book As Workbook
Dim Iss, Plnt, VC, CCA, crit, BOD, emls As Worksheet
Dim LR1, LR2, LReml, lastrow, lastrow2, lastrow3, lastrow4 As Long
Dim olApp As Outlook.Application
Dim olFolder As Outlook.MAPIFolder
Dim olNewmail, olNewmail2 As Outlook.MailItem
Dim rgData, rgCriteria, rgOutput, emailRng, cl As Range
Dim who, BOname, sTo, currentsig, signatfile, TempFile1, TempFile2, TempFile3, Tempfile4 As String
Dim htmlrng As Range
Dim htmlrng2 As Range
Dim htmlrng3 As Range
Dim htmlrng4 As Range
Dim ts As Object
Dim fso As Object

Am I declaring too much?

 

[logandiana]

Anybody??

 

[logandiana]

So I've stumped the forum again?

 

[mikerickson]

AdvancedFilter can be finicky. I assume there are no changes in headers, no stray entries in the blank columns that surround the data.

If it crashes, and you re-load excel and immediately try to do the same filter with the same data, does that crash again?
Do you force the range to be unfiltered before invoking AdvancedFilter.

 

[logandiana]

I assume there are no changes in headers, no stray entries in the blank columns that surround the data.

Nope, no changes. No stray entries.

If it crashes, and you re-load excel and immediately try to do the same filter with the same data, does that crash again?

Sometimes, sometimes not. I can't detect any pattern.

Do you force the range to be unfiltered before invoking AdvancedFilter.

The range would not have any filter on before hand.

 

[JimmySA85]

I’m having the same issue.

but only on one of about twenty users of same File. I’m assuming it’s the pc because quite a bit older and well used compared to the others.

 

[JimmySA85]

I’m having the same issue.

but only on one of about twenty users of same File. I’m assuming it’s the pc because quite a bit older and well used compared to the others.

It happens randomly, it also started happening one day, didn’t happen until the other day.

I have a procedure that uses the advanced filter.

BOD is data on a sheet in another open workbook
crit and Plnt are different sheets in the macro workbook

Dim rgData, rgCriteria, rgOutput As Range

Set rgData = BOD.Range("A1").CurrentRegion  
Set rgCriteria = crit.Range("A1").CurrentRegion
Set rgOutput = Plnt.Range("B" & LR1 + 2).CurrentRegion

rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput    '<<<< this line seems to be the issue

This code DOES work. It runs fast and without issue about 90% of the time.
The other 10% of the time it crashes Excel, no spinning, no error messages, no warning. All Excel just disappears.
This is the same whether running the code or stepping through in debug.
The the criteria never changes. The only data that sometimes changes is the data that's on BOD.
But I have tested it repeatedly with the same data and I always hold my breath when I hit the button.


I can't figure out what causes it though. Is there a different way to write it so that it works 100% of the time?
Is it a computer issue? I have 16gb of RAM and an 8 core i7.
I have other things declared at the top of the module.

Dim OWI, BO, TempWB, book As Workbook
Dim Iss, Plnt, VC, CCA, crit, BOD, emls As Worksheet
Dim LR1, LR2, LReml, lastrow, lastrow2, lastrow3, lastrow4 As Long
Dim olApp As Outlook.Application
Dim olFolder As Outlook.MAPIFolder
Dim olNewmail, olNewmail2 As Outlook.MailItem
Dim rgData, rgCriteria, rgOutput, emailRng, cl As Range
Dim who, BOname, sTo, currentsig, signatfile, TempFile1, TempFile2, TempFile3, Tempfile4 As String
Dim htmlrng As Range
Dim htmlrng2 As Range
Dim htmlrng3 As Range
Dim htmlrng4 As Range
Dim ts As Object
Dim fso As Object

Am I declaring too much?

I declare a lot and never had a problem.

did you bet try the workbook on another PC to see if it behaves the same?

 

[VBasic2008]

Most of your variables are declared as variant. Not that it will help, but try this instead:

Dim OWI As Workbook, BO As Workbook, TempWB As Workbook, book As Workbook
Dim Iss As Worksheet, Plnt As Worksheet, VC As Worksheet, CCA As Worksheet
Dim crit As Worksheet, BOD As Worksheet, emls As Worksheet
Dim LR1 As Long, LR2 As Long, LReml As Long, lastrow As Long, lastrow2 As Long
Dim lastrow3 As Long, lastrow4 As Long
Dim olApp As Outlook.Application
Dim olFolder As Outlook.MAPIFolder
Dim olNewmail As Outlook.MailItem, olNewmail2 As Outlook.MailItem
Dim rgData As Range, rgCriteria As Range, rgOutput As Range, emailRng As Range
Dim cl As Range
Dim who As String, BOname As String, sTo As String, currentsig As String
Dim signatfile As String, TempFile1 As String, TempFile2 As String
Dim TempFile3 As String, Tempfile4 As String
Dim htmlrng As Range
Dim htmlrng2 As Range

Also in your sample code:

Dim rgData As Range, rgCriteria As Range, rgOutput As Range

Btw, I always thought that Advanced Filter cannot copy data to a different worksheet.

 

[logandiana]

Most of your variables are declared as variant. Not that it will help, but try this instead:

VBA Code:
Dim OWI As Workbook, BO As Workbook, TempWB As Workbook, book As Workbook
Dim Iss As Worksheet, Plnt As Worksheet, VC As Worksheet, CCA As Worksheet
Dim crit As Worksheet, BOD As Worksheet, emls As Worksheet
Dim LR1 As Long, LR2 As Long, LReml As Long, lastrow As Long, lastrow2 As Long
Dim lastrow3 As Long, lastrow4 As Long
Dim olApp As Outlook.Application
Dim olFolder As Outlook.MAPIFolder
Dim olNewmail As Outlook.MailItem, olNewmail2 As Outlook.MailItem
Dim rgData As Range, rgCriteria As Range, rgOutput As Range, emailRng As Range
Dim cl As Range
Dim who As String, BOname As String, sTo As String, currentsig As String
Dim signatfile As String, TempFile1 As String, TempFile2 As String
Dim TempFile3 As String, Tempfile4 As String
Dim htmlrng As Range
Dim htmlrng2 As Range

Yes I realized this and changed the way I declare. I don't know where I picked up that it could be done this way, but I had been doing it for a long time. I had to go back and change a ton of workbooks to the correct declarations.

It does seem like this corrected the issue to some degree, as the issue still happens randomly, but no where near as often.

Also in your sample code:

VBA Code:
Dim rgData As Range, rgCriteria As Range, rgOutput As Range
Btw, I always thought that Advanced Filter cannot copy data to a different worksheet.

Copying to another worksheet or workbook in this case, has always worked in all my other projects with no issues ever.
This is the only project with the random shutdown issues.