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

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
94
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

VBA Code:
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.


Code:
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,951
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

Board Regular
Joined
Feb 21, 2017
Messages
94

ADVERTISEMENT

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

New Member
Joined
Oct 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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.


Code:
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

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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

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.
 
Last edited:

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
94
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

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