SimoneFick
08-25-2020, 02:21 AM
I have code in VBA that uses an Advanced filter to fill multiple tables. It worked without any errors before. I upgraded from Office 2013 to Office 365 and suddenly I get error messages on the code...
The Advanced filter runs, fills, and filters the tables correctly but I still get an error message that pops up "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated". If I press okay another error shows "Run-time Error 1004: Advanced Filter method of range class failed". The file contains no formulas, 30500 rows, 37 columns.
EDIT (added tried solutions)
I've searched for solutions online and tried a couple but none have worked so far. I've tried the following:
Closing down all Excel applications and run macro again
closing down all application and only running Excel
Reduce the core processors for Excel from 4 to 2
Turn off the Multi-threaded calculation in Advanced options
Remove all .COM add-ins
Reduce the file to only use 100 rows and not the full file, tried 50 as well
Tried manually using the advanced filter, which doesn't work anymore, I get the same error messages.
I checked all the On Error Resume Next code. They are all enclosed and end with GoTo 0
Tried removing filters on the extract datasheet and copy to range sheet
Tried activating the sheet where I'm copying to first before running the filter
Tried changing the filter criteria to a set range and not whole columns Set CorpOrdCompCrit = .Range("$A$8:$F$10")
I use advanced filter about 20 times in the code, so it's a bit of a problem. One section of the code looks like this (all 20 sections for the advanced filter are structured the same but with different filter criteria, sometimes just 1 filter):
'Create Filter Criteria ranges
With MainWB.Worksheets.Add
.Name = "FltrCrit"
Dim FltrCrit As Worksheet
Set FltrCrit = MainWB.Worksheets("FltrCrit")
End With
With FltrCrit
Dim CorpOrdCompCrit As Range
Dim myLastColumn As Long
'Create Corporate Order Compliance Filter Criteria Range
.Cells(7, "A") = "Corp Order Comp"
.Cells(8, "A") = "MS"
.Cells(9, "A") = "=4"
.Cells(10, "A") = "=4"
.Cells(8, "B") = "SOH"
.Cells(9, "B") = "=0"
.Cells(10, "B") = "=0"
.Cells(8, "C") = "On Order"
.Cells(9, "C") = "=0"
.Cells(10, "C") = "=0"
.Cells(8, "D") = "RP Type"
.Cells(9, "D") = "Roster"
.Cells(10, "D") = "Roster"
.Cells(8, "E") = "Format"
.Cells(9, "E") = "Corporate"
.Cells(10, "E") = "Hyper"
.Cells(8, "F") = "Region"
.Cells(9, "F") = VRegion
.Cells(10, "F") = VRegion
'get last column, set range name
With .Cells
myLastColumn = .Find(What:="*", After:=.Cells(8), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set CorpOrdCompCrit = .Range(.Cells(8, "A:A"), .Cells(10, myLastColumn))
End With
'CORPORATE ORDER COMPLIANCE
Dim tblFiltered As ListObject
Dim copyToRng As Range, SDCRange As Range
Set tblFiltered = wb.Worksheets("Corporate Order Compliance").ListObjects("Table_Corporate_Order_Compliance3")
tblFiltered.AutoFilter.ShowAllData
Set SDCRange = wsSDC.ListObjects("Table_SDCdata").Range
Set copyToRng = tblFiltered.HeaderRowRange
'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
The bottom part is where the error pop's up, on this line for the advanced filter.
'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
Does anyone know why this is suddenly happening and how I can fix the problem, please?
I posted it on StackOverflow and on Excel Forum 2 months ago but no solutions that worked yet, unfortunately.
Links:
https://stackoverflow.com/questions/62540181/excel-vba-with-advanced-filter-error-excel-ran-out-of-resources
https://www.excelforum.com/excel-programming-vba-macros/1320504-excel-vba-with-advanced-filter-error-excel-ran-out-of-resources.html
The Advanced filter runs, fills, and filters the tables correctly but I still get an error message that pops up "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated". If I press okay another error shows "Run-time Error 1004: Advanced Filter method of range class failed". The file contains no formulas, 30500 rows, 37 columns.
EDIT (added tried solutions)
I've searched for solutions online and tried a couple but none have worked so far. I've tried the following:
Closing down all Excel applications and run macro again
closing down all application and only running Excel
Reduce the core processors for Excel from 4 to 2
Turn off the Multi-threaded calculation in Advanced options
Remove all .COM add-ins
Reduce the file to only use 100 rows and not the full file, tried 50 as well
Tried manually using the advanced filter, which doesn't work anymore, I get the same error messages.
I checked all the On Error Resume Next code. They are all enclosed and end with GoTo 0
Tried removing filters on the extract datasheet and copy to range sheet
Tried activating the sheet where I'm copying to first before running the filter
Tried changing the filter criteria to a set range and not whole columns Set CorpOrdCompCrit = .Range("$A$8:$F$10")
I use advanced filter about 20 times in the code, so it's a bit of a problem. One section of the code looks like this (all 20 sections for the advanced filter are structured the same but with different filter criteria, sometimes just 1 filter):
'Create Filter Criteria ranges
With MainWB.Worksheets.Add
.Name = "FltrCrit"
Dim FltrCrit As Worksheet
Set FltrCrit = MainWB.Worksheets("FltrCrit")
End With
With FltrCrit
Dim CorpOrdCompCrit As Range
Dim myLastColumn As Long
'Create Corporate Order Compliance Filter Criteria Range
.Cells(7, "A") = "Corp Order Comp"
.Cells(8, "A") = "MS"
.Cells(9, "A") = "=4"
.Cells(10, "A") = "=4"
.Cells(8, "B") = "SOH"
.Cells(9, "B") = "=0"
.Cells(10, "B") = "=0"
.Cells(8, "C") = "On Order"
.Cells(9, "C") = "=0"
.Cells(10, "C") = "=0"
.Cells(8, "D") = "RP Type"
.Cells(9, "D") = "Roster"
.Cells(10, "D") = "Roster"
.Cells(8, "E") = "Format"
.Cells(9, "E") = "Corporate"
.Cells(10, "E") = "Hyper"
.Cells(8, "F") = "Region"
.Cells(9, "F") = VRegion
.Cells(10, "F") = VRegion
'get last column, set range name
With .Cells
myLastColumn = .Find(What:="*", After:=.Cells(8), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set CorpOrdCompCrit = .Range(.Cells(8, "A:A"), .Cells(10, myLastColumn))
End With
'CORPORATE ORDER COMPLIANCE
Dim tblFiltered As ListObject
Dim copyToRng As Range, SDCRange As Range
Set tblFiltered = wb.Worksheets("Corporate Order Compliance").ListObjects("Table_Corporate_Order_Compliance3")
tblFiltered.AutoFilter.ShowAllData
Set SDCRange = wsSDC.ListObjects("Table_SDCdata").Range
Set copyToRng = tblFiltered.HeaderRowRange
'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
The bottom part is where the error pop's up, on this line for the advanced filter.
'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
Does anyone know why this is suddenly happening and how I can fix the problem, please?
I posted it on StackOverflow and on Excel Forum 2 months ago but no solutions that worked yet, unfortunately.
Links:
https://stackoverflow.com/questions/62540181/excel-vba-with-advanced-filter-error-excel-ran-out-of-resources
https://www.excelforum.com/excel-programming-vba-macros/1320504-excel-vba-with-advanced-filter-error-excel-ran-out-of-resources.html