Memory usage (RAM) of excel stays very high after Macro is executedautomatically execute an Excel macro on a cell changeExcel.exe stays in memory after macro finishesEXCEL VBA - Error when copying from another instance of Excel to active workbookExcel paste special using values only, also copies the data type of the value along with the value into the destination cellHow to prevent Excel VBA paste special values from changing data type from number/date to textImport data from a CSV file into excel from a specific locationExcel VBA Copy/Paste Link from WorksheetExcel Macro: Copying row values from one worksheet to a specific place in another worksheet, based on criteriaTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a looptrouble declaring a variable as a specific cell on a worksheet

Should I refuse to be named as co-author of a low quality paper?

How can powerful telekinesis avoid violating Newton's 3rd Law?

How durable are silver inlays on a blade?

Print "N NE E SE S SW W NW"

Why do radiation hardened IC packages often have long leads?

Is there a DSLR/mirorless camera with minimal options like a classic, simple SLR?

The significance of kelvin as a unit of absolute temperature

Assigning function to function pointer, const argument correctness?

How can I remove material from this wood beam?

Was planting UN flag on Moon ever discussed?

What do Birth, Age, and Death mean in the first noble truth?

That's not my X, its Y is too Z

Rail-to-rail op-amp only reaches 90% of VCC, works sometimes, not everytime

Converting from CMYK to RGB (to work with it), then back to CMYK

Why is Na5 not played in this line of the French Defense, Advance Variation?

NUL delimited variable

Multiband vertical antenna not working as expected

C++ logging library

What is the reason for setting flaps 1 on the ground at high temperatures?

Do you really need a KDF when you have a PRF?

I've been given a project I can't complete, what should I do?

What are the unintended or dangerous consequences of allowing spells that target and damage creatures to also target and damage objects?

How can one's career as a reviewer be ended?

Wizard clothing for warm weather



Memory usage (RAM) of excel stays very high after Macro is executed


automatically execute an Excel macro on a cell changeExcel.exe stays in memory after macro finishesEXCEL VBA - Error when copying from another instance of Excel to active workbookExcel paste special using values only, also copies the data type of the value along with the value into the destination cellHow to prevent Excel VBA paste special values from changing data type from number/date to textImport data from a CSV file into excel from a specific locationExcel VBA Copy/Paste Link from WorksheetExcel Macro: Copying row values from one worksheet to a specific place in another worksheet, based on criteriaTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a looptrouble declaring a variable as a specific cell on a worksheet






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;








0















This subject is not new on the web, yet not really documented.



I have an Excel document with 2 events (macros) that, after being executed, keep slowing down my Excel, eating up to 7GB or RAM (out of 16). This is insane. So far, the only solution I found to reset the memory is to re-open the document (~150MB memory usage on start).



Here is a 1mn video showing strange behaviour of memory usage in my task manager after first event (see details below). When I run the 2nd macro (around 30s in the video), the memory almost resets, but right after the macro is finished, the memory keeps getting higher and higher even though nothing is executed on the workbook.



• The first event is a manual Refresh All. My document has 36 connection only queries with 7 transformative steps (each query is linked to a table) and the last query combine them all in a source table which is linked to a pivot table and charts. It runs smoothly but stucks the ram from 150MB to 4GB, even though the refresh takes only 2 seconds.



• The second query is a csv import and copy/paste some data into one table. This gets the ram even higher (6/7GB) even though the macro runs in 2 seconds. Every time I re-run this macro, the RAM resets to say 600MB, then climbs back to 7GB even though nothing is occuring anymore.



The code of this macro deletes the query and the sheet on which the csv is imported at the end of the process, plus I also tried "Application.CutCopyMode = False" in case my clipboard was responsible. But no, it's way bigger than that.



Here is the code of the second macro (bear in mind I'm new to VBA) :



Sub importcsvdata()
Dim ActWks As Worksheet
Set ActWks = ActiveSheet
sPath = Application.GetOpenFilename()
On Error Resume Next
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 With ThisWorkbook
 .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "tempo-csv"
 End With
 Application.ScreenUpdating = False
 ActiveWorkbook.Queries.Add Name:="tempo-csv", Formula:= _
 "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & sPath & """),[Delimiter="","", Columns=68, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source)," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",""Name"", type text, ""Email""" & _
 ", type text, ""Financial Status"", type text, ""Paid at"", type datetimezone, ""Fulfillment Status"", type text, ""Fulfilled at"", type datetimezone, ""Accepts Marketing"", type text, ""Currency"", type text, ""Subtotal"", type text, ""Shipping"", type text, ""Taxes"", type text, ""Total"", type text, ""Discount Code"", type text, ""Discount " & _
 "Amount"", type text, ""Shipping Method"", type text, ""Created at"", type datetimezone, ""Lineitem quantity"", Int64.Type, ""Lineitem name"", type text, ""Lineitem price"", type text, ""Lineitem compare at price"", type text, ""Lineitem sku"", type text, ""Lineitem requires shipping"", type logical, ""Lineitem taxable"", type logical, ""Lineitem " & _
 "fulfillment status"", type text, ""Billing Name"", type text, ""Billing Street"", type text, ""Billing Address1"", type text, ""Billing Address2"", type text, ""Billing Company"", type text, ""Billing City"", type text, ""Billing Zip"", type text, ""Billing Province"", type text, ""Billing Country"", type text, ""Billing Phone"", Int64.Type, """ & _
 "Shipping Name"", type text, ""Shipping Street"", type text, ""Shipping Address1"", type text, ""Shipping Address2"", type text, ""Shipping Company"", type text, ""Shipping City"", type text, ""Shipping Zip"", type text, ""Shipping Province"", type text, ""Shipping Country"", type text, ""Shipping Phone"", Int64.Type, ""Notes"", type text, ""Not" & _
 "e Attributes"", type text, ""Cancelled at"", type text, ""Payment Method"", type text, ""Payment Reference"", type text, ""Refunded Amount"", type text, ""Vendor"", type text, ""Id"", Int64.Type, ""Tags"", type text, ""Risk Level"", type text, ""Source"", type text, ""Lineitem discount"", type text, ""Tax 1 Name"", type text, ""Tax 1 Value""," & _
 " type text, ""Tax 2 Name"", type text, ""Tax 2 Value"", type text, ""Tax 3 Name"", type text, ""Tax 3 Value"", type text, ""Tax 4 Name"", type text, ""Tax 4 Value"", type text, ""Tax 5 Name"", type text, ""Tax 5 Value"", type text, ""Phone"", Int64.Type, ""Receipt Number"", type text)," & Chr(13) & "" & Chr(10) & " #""Extracted Date"" = Table.TransformColumns(#""Change" & _
 "d Type"",""Paid at"", DateTime.Date)," & Chr(13) & "" & Chr(10) & " #""Sorted Rows"" = Table.Sort(#""Extracted Date"",""Paid at"", Order.Ascending)," & Chr(13) & "" & Chr(10) & " #""Replaced Value"" = Table.ReplaceValue(#""Sorted Rows"",""."","","",Replacer.ReplaceText,""Total"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Replaced Value"",""Total"", Currency.Type)," & Chr(13) & "" & Chr(10) & " #""Added Conditio" & _
 "nal Column"" = Table.AddColumn(#""Changed Type1"", ""Custom"", each if Text.Contains([Payment Method], ""PayPal"") then ""PayPal"" else if Text.Contains([Payment Method], ""Stripe"") then ""Stripe"" else null )" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Added Conditional Column"""
 Application.ScreenUpdating = False
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
 "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""tempo-csv""" _
 , Destination:=Range("$A$1")).QueryTable
 .CommandType = xlCmdSql
 .CommandText = Array("SELECT * FROM [tempo-csv]")
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .PreserveColumnInfo = False
 .ListObject.DisplayName = "tempo_table"
 .Refresh BackgroundQuery:=False
 End With
 ActWks.Activate
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 If ActiveSheet.Name = "Janvier" Then
 ActiveSheet.ListObjects("Recettes_janvier").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Date de commande]").End(Excel.xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Février" Then
 ActiveSheet.ListObjects("Recettes_février").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("février").Range("Recettes_février[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("février").Range("Recettes_février[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("février").Range("Recettes_février[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("février").Range("Recettes_février[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("février").Range("Recettes_février[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("février").Range("Recettes_février[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mars" Then
 ActiveSheet.ListObjects("Recettes_mars").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mars").Range("B14:B1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mars").Range("C14:C1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mars").Range("D14:D1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mars").Range("E14:E1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mars").Range("F14:F1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mars").Range("G14:G1500").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Avril" Then
 ActiveSheet.ListObjects("Recettes_avril").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("avril").Range("Recettes_avril[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("avril").Range("Recettes_avril[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("avril").Range("Recettes_avril[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("avril").Range("Recettes_avril[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("avril").Range("Recettes_avril[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("avril").Range("Recettes_avril[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mai" Then
 ActiveSheet.ListObjects("Recettes_mai").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mai").Range("Recettes_mai[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mai").Range("Recettes_mai[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mai").Range("Recettes_mai[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mai").Range("Recettes_mai[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mai").Range("Recettes_mai[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mai").Range("Recettes_mai[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juin" Then
 ActiveSheet.ListObjects("Recettes_juin").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juin").Range("Recettes_juin[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juin").Range("Recettes_juin[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juin").Range("Recettes_juin[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juin").Range("Recettes_juin[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juin").Range("Recettes_juin[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juin").Range("Recettes_juin[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juillet" Then
 ActiveSheet.ListObjects("Recettes_juillet").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Août" Then
 ActiveSheet.ListObjects("Recettes_août").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("août").Range("Recettes_août[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("août").Range("Recettes_août[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("août").Range("Recettes_août[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("août").Range("Recettes_août[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("août").Range("Recettes_août[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("août").Range("Recettes_août[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Septembre" Then
 ActiveSheet.ListObjects("Recettes_septembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Octobre" Then
 ActiveSheet.ListObjects("Recettes_octobre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Novembre" Then
 ActiveSheet.ListObjects("Recettes_novembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Décembre" Then
 ActiveSheet.ListObjects("Recettes_décembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 Else:
 End If
 Sheets("tempo-csv").Delete
 ActiveWorkbook.Queries("tempo-csv").Delete
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 Application.CutCopyMode = False
 Application.EnableEvents = True
End Sub


What could help resolve this memory issue ? I found no way to reset the memory usage. Besides, I'm not even sure where the problem lies.






excel vba ram







share|improve this question






















  • Tip #1: Take a look at your code. There's a lot of duplicated lines. They are reused depending on name of active sheet. You have to push it into subroutine. Tip#2: Do not use Copy-PasteSpecial. This method is very high memory consuming. Replace it with DestinationSheet.Range("Something") = SourceSheet.Range("Whatever")

    – Maciej Los
    Mar 24 at 21:55












  • Since you only want Value from the ranges, you could have a Variant variable to store the columns Value, then assign the destination's value from the Variant.

    – PatricK
    Mar 25 at 0:54

















0















This subject is not new on the web, yet not really documented.



I have an Excel document with 2 events (macros) that, after being executed, keep slowing down my Excel, eating up to 7GB or RAM (out of 16). This is insane. So far, the only solution I found to reset the memory is to re-open the document (~150MB memory usage on start).



Here is a 1mn video showing strange behaviour of memory usage in my task manager after first event (see details below). When I run the 2nd macro (around 30s in the video), the memory almost resets, but right after the macro is finished, the memory keeps getting higher and higher even though nothing is executed on the workbook.



• The first event is a manual Refresh All. My document has 36 connection only queries with 7 transformative steps (each query is linked to a table) and the last query combine them all in a source table which is linked to a pivot table and charts. It runs smoothly but stucks the ram from 150MB to 4GB, even though the refresh takes only 2 seconds.



• The second query is a csv import and copy/paste some data into one table. This gets the ram even higher (6/7GB) even though the macro runs in 2 seconds. Every time I re-run this macro, the RAM resets to say 600MB, then climbs back to 7GB even though nothing is occuring anymore.



The code of this macro deletes the query and the sheet on which the csv is imported at the end of the process, plus I also tried "Application.CutCopyMode = False" in case my clipboard was responsible. But no, it's way bigger than that.



Here is the code of the second macro (bear in mind I'm new to VBA) :



Sub importcsvdata()
Dim ActWks As Worksheet
Set ActWks = ActiveSheet
sPath = Application.GetOpenFilename()
On Error Resume Next
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 With ThisWorkbook
 .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "tempo-csv"
 End With
 Application.ScreenUpdating = False
 ActiveWorkbook.Queries.Add Name:="tempo-csv", Formula:= _
 "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & sPath & """),[Delimiter="","", Columns=68, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source)," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",""Name"", type text, ""Email""" & _
 ", type text, ""Financial Status"", type text, ""Paid at"", type datetimezone, ""Fulfillment Status"", type text, ""Fulfilled at"", type datetimezone, ""Accepts Marketing"", type text, ""Currency"", type text, ""Subtotal"", type text, ""Shipping"", type text, ""Taxes"", type text, ""Total"", type text, ""Discount Code"", type text, ""Discount " & _
 "Amount"", type text, ""Shipping Method"", type text, ""Created at"", type datetimezone, ""Lineitem quantity"", Int64.Type, ""Lineitem name"", type text, ""Lineitem price"", type text, ""Lineitem compare at price"", type text, ""Lineitem sku"", type text, ""Lineitem requires shipping"", type logical, ""Lineitem taxable"", type logical, ""Lineitem " & _
 "fulfillment status"", type text, ""Billing Name"", type text, ""Billing Street"", type text, ""Billing Address1"", type text, ""Billing Address2"", type text, ""Billing Company"", type text, ""Billing City"", type text, ""Billing Zip"", type text, ""Billing Province"", type text, ""Billing Country"", type text, ""Billing Phone"", Int64.Type, """ & _
 "Shipping Name"", type text, ""Shipping Street"", type text, ""Shipping Address1"", type text, ""Shipping Address2"", type text, ""Shipping Company"", type text, ""Shipping City"", type text, ""Shipping Zip"", type text, ""Shipping Province"", type text, ""Shipping Country"", type text, ""Shipping Phone"", Int64.Type, ""Notes"", type text, ""Not" & _
 "e Attributes"", type text, ""Cancelled at"", type text, ""Payment Method"", type text, ""Payment Reference"", type text, ""Refunded Amount"", type text, ""Vendor"", type text, ""Id"", Int64.Type, ""Tags"", type text, ""Risk Level"", type text, ""Source"", type text, ""Lineitem discount"", type text, ""Tax 1 Name"", type text, ""Tax 1 Value""," & _
 " type text, ""Tax 2 Name"", type text, ""Tax 2 Value"", type text, ""Tax 3 Name"", type text, ""Tax 3 Value"", type text, ""Tax 4 Name"", type text, ""Tax 4 Value"", type text, ""Tax 5 Name"", type text, ""Tax 5 Value"", type text, ""Phone"", Int64.Type, ""Receipt Number"", type text)," & Chr(13) & "" & Chr(10) & " #""Extracted Date"" = Table.TransformColumns(#""Change" & _
 "d Type"",""Paid at"", DateTime.Date)," & Chr(13) & "" & Chr(10) & " #""Sorted Rows"" = Table.Sort(#""Extracted Date"",""Paid at"", Order.Ascending)," & Chr(13) & "" & Chr(10) & " #""Replaced Value"" = Table.ReplaceValue(#""Sorted Rows"",""."","","",Replacer.ReplaceText,""Total"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Replaced Value"",""Total"", Currency.Type)," & Chr(13) & "" & Chr(10) & " #""Added Conditio" & _
 "nal Column"" = Table.AddColumn(#""Changed Type1"", ""Custom"", each if Text.Contains([Payment Method], ""PayPal"") then ""PayPal"" else if Text.Contains([Payment Method], ""Stripe"") then ""Stripe"" else null )" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Added Conditional Column"""
 Application.ScreenUpdating = False
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
 "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""tempo-csv""" _
 , Destination:=Range("$A$1")).QueryTable
 .CommandType = xlCmdSql
 .CommandText = Array("SELECT * FROM [tempo-csv]")
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .PreserveColumnInfo = False
 .ListObject.DisplayName = "tempo_table"
 .Refresh BackgroundQuery:=False
 End With
 ActWks.Activate
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 If ActiveSheet.Name = "Janvier" Then
 ActiveSheet.ListObjects("Recettes_janvier").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Date de commande]").End(Excel.xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Février" Then
 ActiveSheet.ListObjects("Recettes_février").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("février").Range("Recettes_février[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("février").Range("Recettes_février[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("février").Range("Recettes_février[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("février").Range("Recettes_février[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("février").Range("Recettes_février[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("février").Range("Recettes_février[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mars" Then
 ActiveSheet.ListObjects("Recettes_mars").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mars").Range("B14:B1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mars").Range("C14:C1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mars").Range("D14:D1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mars").Range("E14:E1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mars").Range("F14:F1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mars").Range("G14:G1500").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Avril" Then
 ActiveSheet.ListObjects("Recettes_avril").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("avril").Range("Recettes_avril[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("avril").Range("Recettes_avril[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("avril").Range("Recettes_avril[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("avril").Range("Recettes_avril[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("avril").Range("Recettes_avril[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("avril").Range("Recettes_avril[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mai" Then
 ActiveSheet.ListObjects("Recettes_mai").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mai").Range("Recettes_mai[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mai").Range("Recettes_mai[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mai").Range("Recettes_mai[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mai").Range("Recettes_mai[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mai").Range("Recettes_mai[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mai").Range("Recettes_mai[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juin" Then
 ActiveSheet.ListObjects("Recettes_juin").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juin").Range("Recettes_juin[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juin").Range("Recettes_juin[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juin").Range("Recettes_juin[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juin").Range("Recettes_juin[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juin").Range("Recettes_juin[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juin").Range("Recettes_juin[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juillet" Then
 ActiveSheet.ListObjects("Recettes_juillet").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Août" Then
 ActiveSheet.ListObjects("Recettes_août").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("août").Range("Recettes_août[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("août").Range("Recettes_août[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("août").Range("Recettes_août[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("août").Range("Recettes_août[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("août").Range("Recettes_août[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("août").Range("Recettes_août[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Septembre" Then
 ActiveSheet.ListObjects("Recettes_septembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Octobre" Then
 ActiveSheet.ListObjects("Recettes_octobre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Novembre" Then
 ActiveSheet.ListObjects("Recettes_novembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Décembre" Then
 ActiveSheet.ListObjects("Recettes_décembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 Else:
 End If
 Sheets("tempo-csv").Delete
 ActiveWorkbook.Queries("tempo-csv").Delete
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 Application.CutCopyMode = False
 Application.EnableEvents = True
End Sub


What could help resolve this memory issue ? I found no way to reset the memory usage. Besides, I'm not even sure where the problem lies.






excel vba ram







share|improve this question






















  • Tip #1: Take a look at your code. There's a lot of duplicated lines. They are reused depending on name of active sheet. You have to push it into subroutine. Tip#2: Do not use Copy-PasteSpecial. This method is very high memory consuming. Replace it with DestinationSheet.Range("Something") = SourceSheet.Range("Whatever")

    – Maciej Los
    Mar 24 at 21:55












  • Since you only want Value from the ranges, you could have a Variant variable to store the columns Value, then assign the destination's value from the Variant.

    – PatricK
    Mar 25 at 0:54













0












0








0








This subject is not new on the web, yet not really documented.



I have an Excel document with 2 events (macros) that, after being executed, keep slowing down my Excel, eating up to 7GB or RAM (out of 16). This is insane. So far, the only solution I found to reset the memory is to re-open the document (~150MB memory usage on start).



Here is a 1mn video showing strange behaviour of memory usage in my task manager after first event (see details below). When I run the 2nd macro (around 30s in the video), the memory almost resets, but right after the macro is finished, the memory keeps getting higher and higher even though nothing is executed on the workbook.



• The first event is a manual Refresh All. My document has 36 connection only queries with 7 transformative steps (each query is linked to a table) and the last query combine them all in a source table which is linked to a pivot table and charts. It runs smoothly but stucks the ram from 150MB to 4GB, even though the refresh takes only 2 seconds.



• The second query is a csv import and copy/paste some data into one table. This gets the ram even higher (6/7GB) even though the macro runs in 2 seconds. Every time I re-run this macro, the RAM resets to say 600MB, then climbs back to 7GB even though nothing is occuring anymore.



The code of this macro deletes the query and the sheet on which the csv is imported at the end of the process, plus I also tried "Application.CutCopyMode = False" in case my clipboard was responsible. But no, it's way bigger than that.



Here is the code of the second macro (bear in mind I'm new to VBA) :



Sub importcsvdata()
Dim ActWks As Worksheet
Set ActWks = ActiveSheet
sPath = Application.GetOpenFilename()
On Error Resume Next
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 With ThisWorkbook
 .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "tempo-csv"
 End With
 Application.ScreenUpdating = False
 ActiveWorkbook.Queries.Add Name:="tempo-csv", Formula:= _
 "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & sPath & """),[Delimiter="","", Columns=68, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source)," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",""Name"", type text, ""Email""" & _
 ", type text, ""Financial Status"", type text, ""Paid at"", type datetimezone, ""Fulfillment Status"", type text, ""Fulfilled at"", type datetimezone, ""Accepts Marketing"", type text, ""Currency"", type text, ""Subtotal"", type text, ""Shipping"", type text, ""Taxes"", type text, ""Total"", type text, ""Discount Code"", type text, ""Discount " & _
 "Amount"", type text, ""Shipping Method"", type text, ""Created at"", type datetimezone, ""Lineitem quantity"", Int64.Type, ""Lineitem name"", type text, ""Lineitem price"", type text, ""Lineitem compare at price"", type text, ""Lineitem sku"", type text, ""Lineitem requires shipping"", type logical, ""Lineitem taxable"", type logical, ""Lineitem " & _
 "fulfillment status"", type text, ""Billing Name"", type text, ""Billing Street"", type text, ""Billing Address1"", type text, ""Billing Address2"", type text, ""Billing Company"", type text, ""Billing City"", type text, ""Billing Zip"", type text, ""Billing Province"", type text, ""Billing Country"", type text, ""Billing Phone"", Int64.Type, """ & _
 "Shipping Name"", type text, ""Shipping Street"", type text, ""Shipping Address1"", type text, ""Shipping Address2"", type text, ""Shipping Company"", type text, ""Shipping City"", type text, ""Shipping Zip"", type text, ""Shipping Province"", type text, ""Shipping Country"", type text, ""Shipping Phone"", Int64.Type, ""Notes"", type text, ""Not" & _
 "e Attributes"", type text, ""Cancelled at"", type text, ""Payment Method"", type text, ""Payment Reference"", type text, ""Refunded Amount"", type text, ""Vendor"", type text, ""Id"", Int64.Type, ""Tags"", type text, ""Risk Level"", type text, ""Source"", type text, ""Lineitem discount"", type text, ""Tax 1 Name"", type text, ""Tax 1 Value""," & _
 " type text, ""Tax 2 Name"", type text, ""Tax 2 Value"", type text, ""Tax 3 Name"", type text, ""Tax 3 Value"", type text, ""Tax 4 Name"", type text, ""Tax 4 Value"", type text, ""Tax 5 Name"", type text, ""Tax 5 Value"", type text, ""Phone"", Int64.Type, ""Receipt Number"", type text)," & Chr(13) & "" & Chr(10) & " #""Extracted Date"" = Table.TransformColumns(#""Change" & _
 "d Type"",""Paid at"", DateTime.Date)," & Chr(13) & "" & Chr(10) & " #""Sorted Rows"" = Table.Sort(#""Extracted Date"",""Paid at"", Order.Ascending)," & Chr(13) & "" & Chr(10) & " #""Replaced Value"" = Table.ReplaceValue(#""Sorted Rows"",""."","","",Replacer.ReplaceText,""Total"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Replaced Value"",""Total"", Currency.Type)," & Chr(13) & "" & Chr(10) & " #""Added Conditio" & _
 "nal Column"" = Table.AddColumn(#""Changed Type1"", ""Custom"", each if Text.Contains([Payment Method], ""PayPal"") then ""PayPal"" else if Text.Contains([Payment Method], ""Stripe"") then ""Stripe"" else null )" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Added Conditional Column"""
 Application.ScreenUpdating = False
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
 "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""tempo-csv""" _
 , Destination:=Range("$A$1")).QueryTable
 .CommandType = xlCmdSql
 .CommandText = Array("SELECT * FROM [tempo-csv]")
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .PreserveColumnInfo = False
 .ListObject.DisplayName = "tempo_table"
 .Refresh BackgroundQuery:=False
 End With
 ActWks.Activate
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 If ActiveSheet.Name = "Janvier" Then
 ActiveSheet.ListObjects("Recettes_janvier").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Date de commande]").End(Excel.xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Février" Then
 ActiveSheet.ListObjects("Recettes_février").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("février").Range("Recettes_février[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("février").Range("Recettes_février[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("février").Range("Recettes_février[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("février").Range("Recettes_février[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("février").Range("Recettes_février[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("février").Range("Recettes_février[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mars" Then
 ActiveSheet.ListObjects("Recettes_mars").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mars").Range("B14:B1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mars").Range("C14:C1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mars").Range("D14:D1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mars").Range("E14:E1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mars").Range("F14:F1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mars").Range("G14:G1500").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Avril" Then
 ActiveSheet.ListObjects("Recettes_avril").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("avril").Range("Recettes_avril[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("avril").Range("Recettes_avril[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("avril").Range("Recettes_avril[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("avril").Range("Recettes_avril[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("avril").Range("Recettes_avril[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("avril").Range("Recettes_avril[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mai" Then
 ActiveSheet.ListObjects("Recettes_mai").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mai").Range("Recettes_mai[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mai").Range("Recettes_mai[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mai").Range("Recettes_mai[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mai").Range("Recettes_mai[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mai").Range("Recettes_mai[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mai").Range("Recettes_mai[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juin" Then
 ActiveSheet.ListObjects("Recettes_juin").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juin").Range("Recettes_juin[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juin").Range("Recettes_juin[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juin").Range("Recettes_juin[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juin").Range("Recettes_juin[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juin").Range("Recettes_juin[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juin").Range("Recettes_juin[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juillet" Then
 ActiveSheet.ListObjects("Recettes_juillet").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Août" Then
 ActiveSheet.ListObjects("Recettes_août").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("août").Range("Recettes_août[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("août").Range("Recettes_août[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("août").Range("Recettes_août[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("août").Range("Recettes_août[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("août").Range("Recettes_août[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("août").Range("Recettes_août[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Septembre" Then
 ActiveSheet.ListObjects("Recettes_septembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Octobre" Then
 ActiveSheet.ListObjects("Recettes_octobre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Novembre" Then
 ActiveSheet.ListObjects("Recettes_novembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Décembre" Then
 ActiveSheet.ListObjects("Recettes_décembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 Else:
 End If
 Sheets("tempo-csv").Delete
 ActiveWorkbook.Queries("tempo-csv").Delete
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 Application.CutCopyMode = False
 Application.EnableEvents = True
End Sub


What could help resolve this memory issue ? I found no way to reset the memory usage. Besides, I'm not even sure where the problem lies.






excel vba ram







share|improve this question














This subject is not new on the web, yet not really documented.



I have an Excel document with 2 events (macros) that, after being executed, keep slowing down my Excel, eating up to 7GB or RAM (out of 16). This is insane. So far, the only solution I found to reset the memory is to re-open the document (~150MB memory usage on start).



Here is a 1mn video showing strange behaviour of memory usage in my task manager after first event (see details below). When I run the 2nd macro (around 30s in the video), the memory almost resets, but right after the macro is finished, the memory keeps getting higher and higher even though nothing is executed on the workbook.



• The first event is a manual Refresh All. My document has 36 connection only queries with 7 transformative steps (each query is linked to a table) and the last query combine them all in a source table which is linked to a pivot table and charts. It runs smoothly but stucks the ram from 150MB to 4GB, even though the refresh takes only 2 seconds.



• The second query is a csv import and copy/paste some data into one table. This gets the ram even higher (6/7GB) even though the macro runs in 2 seconds. Every time I re-run this macro, the RAM resets to say 600MB, then climbs back to 7GB even though nothing is occuring anymore.



The code of this macro deletes the query and the sheet on which the csv is imported at the end of the process, plus I also tried "Application.CutCopyMode = False" in case my clipboard was responsible. But no, it's way bigger than that.



Here is the code of the second macro (bear in mind I'm new to VBA) :



Sub importcsvdata()
Dim ActWks As Worksheet
Set ActWks = ActiveSheet
sPath = Application.GetOpenFilename()
On Error Resume Next
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 With ThisWorkbook
 .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "tempo-csv"
 End With
 Application.ScreenUpdating = False
 ActiveWorkbook.Queries.Add Name:="tempo-csv", Formula:= _
 "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & sPath & """),[Delimiter="","", Columns=68, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source)," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",""Name"", type text, ""Email""" & _
 ", type text, ""Financial Status"", type text, ""Paid at"", type datetimezone, ""Fulfillment Status"", type text, ""Fulfilled at"", type datetimezone, ""Accepts Marketing"", type text, ""Currency"", type text, ""Subtotal"", type text, ""Shipping"", type text, ""Taxes"", type text, ""Total"", type text, ""Discount Code"", type text, ""Discount " & _
 "Amount"", type text, ""Shipping Method"", type text, ""Created at"", type datetimezone, ""Lineitem quantity"", Int64.Type, ""Lineitem name"", type text, ""Lineitem price"", type text, ""Lineitem compare at price"", type text, ""Lineitem sku"", type text, ""Lineitem requires shipping"", type logical, ""Lineitem taxable"", type logical, ""Lineitem " & _
 "fulfillment status"", type text, ""Billing Name"", type text, ""Billing Street"", type text, ""Billing Address1"", type text, ""Billing Address2"", type text, ""Billing Company"", type text, ""Billing City"", type text, ""Billing Zip"", type text, ""Billing Province"", type text, ""Billing Country"", type text, ""Billing Phone"", Int64.Type, """ & _
 "Shipping Name"", type text, ""Shipping Street"", type text, ""Shipping Address1"", type text, ""Shipping Address2"", type text, ""Shipping Company"", type text, ""Shipping City"", type text, ""Shipping Zip"", type text, ""Shipping Province"", type text, ""Shipping Country"", type text, ""Shipping Phone"", Int64.Type, ""Notes"", type text, ""Not" & _
 "e Attributes"", type text, ""Cancelled at"", type text, ""Payment Method"", type text, ""Payment Reference"", type text, ""Refunded Amount"", type text, ""Vendor"", type text, ""Id"", Int64.Type, ""Tags"", type text, ""Risk Level"", type text, ""Source"", type text, ""Lineitem discount"", type text, ""Tax 1 Name"", type text, ""Tax 1 Value""," & _
 " type text, ""Tax 2 Name"", type text, ""Tax 2 Value"", type text, ""Tax 3 Name"", type text, ""Tax 3 Value"", type text, ""Tax 4 Name"", type text, ""Tax 4 Value"", type text, ""Tax 5 Name"", type text, ""Tax 5 Value"", type text, ""Phone"", Int64.Type, ""Receipt Number"", type text)," & Chr(13) & "" & Chr(10) & " #""Extracted Date"" = Table.TransformColumns(#""Change" & _
 "d Type"",""Paid at"", DateTime.Date)," & Chr(13) & "" & Chr(10) & " #""Sorted Rows"" = Table.Sort(#""Extracted Date"",""Paid at"", Order.Ascending)," & Chr(13) & "" & Chr(10) & " #""Replaced Value"" = Table.ReplaceValue(#""Sorted Rows"",""."","","",Replacer.ReplaceText,""Total"")," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Replaced Value"",""Total"", Currency.Type)," & Chr(13) & "" & Chr(10) & " #""Added Conditio" & _
 "nal Column"" = Table.AddColumn(#""Changed Type1"", ""Custom"", each if Text.Contains([Payment Method], ""PayPal"") then ""PayPal"" else if Text.Contains([Payment Method], ""Stripe"") then ""Stripe"" else null )" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Added Conditional Column"""
 Application.ScreenUpdating = False
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
 "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""tempo-csv""" _
 , Destination:=Range("$A$1")).QueryTable
 .CommandType = xlCmdSql
 .CommandText = Array("SELECT * FROM [tempo-csv]")
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .PreserveColumnInfo = False
 .ListObject.DisplayName = "tempo_table"
 .Refresh BackgroundQuery:=False
 End With
 ActWks.Activate
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 If ActiveSheet.Name = "Janvier" Then
 ActiveSheet.ListObjects("Recettes_janvier").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Date de commande]").End(Excel.xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("janvier").Range("Recettes_janvier[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Février" Then
 ActiveSheet.ListObjects("Recettes_février").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("février").Range("Recettes_février[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("février").Range("Recettes_février[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("février").Range("Recettes_février[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("février").Range("Recettes_février[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("février").Range("Recettes_février[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("février").Range("Recettes_février[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mars" Then
 ActiveSheet.ListObjects("Recettes_mars").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mars").Range("B14:B1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mars").Range("C14:C1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mars").Range("D14:D1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mars").Range("E14:E1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mars").Range("F14:F1500").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mars").Range("G14:G1500").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Avril" Then
 ActiveSheet.ListObjects("Recettes_avril").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("avril").Range("Recettes_avril[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("avril").Range("Recettes_avril[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("avril").Range("Recettes_avril[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("avril").Range("Recettes_avril[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("avril").Range("Recettes_avril[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("avril").Range("Recettes_avril[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Mai" Then
 ActiveSheet.ListObjects("Recettes_mai").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("mai").Range("Recettes_mai[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("mai").Range("Recettes_mai[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("mai").Range("Recettes_mai[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("mai").Range("Recettes_mai[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("mai").Range("Recettes_mai[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("mai").Range("Recettes_mai[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juin" Then
 ActiveSheet.ListObjects("Recettes_juin").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juin").Range("Recettes_juin[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juin").Range("Recettes_juin[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juin").Range("Recettes_juin[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juin").Range("Recettes_juin[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juin").Range("Recettes_juin[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juin").Range("Recettes_juin[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Juillet" Then
 ActiveSheet.ListObjects("Recettes_juillet").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("juillet").Range("Recettes_juillet[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Août" Then
 ActiveSheet.ListObjects("Recettes_août").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("août").Range("Recettes_août[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("août").Range("Recettes_août[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("août").Range("Recettes_août[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("août").Range("Recettes_août[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("août").Range("Recettes_août[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("août").Range("Recettes_août[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Septembre" Then
 ActiveSheet.ListObjects("Recettes_septembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("septembre").Range("Recettes_septembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Octobre" Then
 ActiveSheet.ListObjects("Recettes_octobre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("octobre").Range("Recettes_octobre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Novembre" Then
 ActiveSheet.ListObjects("Recettes_novembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("novembre").Range("Recettes_novembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 ElseIf ActiveSheet.Name = "Décembre" Then
 ActiveSheet.ListObjects("Recettes_décembre").ListRows.Add alwaysinsert:=True
 Worksheets("tempo-csv").Range("tempo_table[Paid at]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Date de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[N° de commande]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Billing Name]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Client]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Vendor]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Type]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Total]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Montant]").Find("").PasteSpecial Paste:=xlPasteValues
 Worksheets("tempo-csv").Range("tempo_table[Custom]").Copy
 Worksheets("décembre").Range("Recettes_décembre[Paiement]").Find("").PasteSpecial Paste:=xlPasteValues
 Else:
 End If
 Sheets("tempo-csv").Delete
 ActiveWorkbook.Queries("tempo-csv").Delete
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 Application.CutCopyMode = False
 Application.EnableEvents = True
End Sub


What could help resolve this memory issue ? I found no way to reset the memory usage. Besides, I'm not even sure where the problem lies.






excel vba ram




excel vba ram






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Mar 24 at 21:42









MaxMax

396




396












  • Tip #1: Take a look at your code. There's a lot of duplicated lines. They are reused depending on name of active sheet. You have to push it into subroutine. Tip#2: Do not use Copy-PasteSpecial. This method is very high memory consuming. Replace it with DestinationSheet.Range("Something") = SourceSheet.Range("Whatever")

    – Maciej Los
    Mar 24 at 21:55












  • Since you only want Value from the ranges, you could have a Variant variable to store the columns Value, then assign the destination's value from the Variant.

    – PatricK
    Mar 25 at 0:54

















  • Tip #1: Take a look at your code. There's a lot of duplicated lines. They are reused depending on name of active sheet. You have to push it into subroutine. Tip#2: Do not use Copy-PasteSpecial. This method is very high memory consuming. Replace it with DestinationSheet.Range("Something") = SourceSheet.Range("Whatever")

    – Maciej Los
    Mar 24 at 21:55












  • Since you only want Value from the ranges, you could have a Variant variable to store the columns Value, then assign the destination's value from the Variant.

    – PatricK
    Mar 25 at 0:54
















Tip #1: Take a look at your code. There's a lot of duplicated lines. They are reused depending on name of active sheet. You have to push it into subroutine. Tip#2: Do not use Copy-PasteSpecial. This method is very high memory consuming. Replace it with DestinationSheet.Range("Something") = SourceSheet.Range("Whatever")

– Maciej Los
Mar 24 at 21:55






Tip #1: Take a look at your code. There's a lot of duplicated lines. They are reused depending on name of active sheet. You have to push it into subroutine. Tip#2: Do not use Copy-PasteSpecial. This method is very high memory consuming. Replace it with DestinationSheet.Range("Something") = SourceSheet.Range("Whatever")

– Maciej Los
Mar 24 at 21:55














Since you only want Value from the ranges, you could have a Variant variable to store the columns Value, then assign the destination's value from the Variant.

– PatricK
Mar 25 at 0:54





Since you only want Value from the ranges, you could have a Variant variable to store the columns Value, then assign the destination's value from the Variant.

– PatricK
Mar 25 at 0:54












0






active

oldest

votes












Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55328858%2fmemory-usage-ram-of-excel-stays-very-high-after-macro-is-executed%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55328858%2fmemory-usage-ram-of-excel-stays-very-high-after-macro-is-executed%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Kamusi Yaliyomo Aina za kamusi | Muundo wa kamusi | Faida za kamusi | Dhima ya picha katika kamusi | Marejeo | Tazama pia | Viungo vya nje | UrambazajiKuhusu kamusiGo-SwahiliWiki-KamusiKamusi ya Kiswahili na Kiingerezakuihariri na kuongeza habari

Image
Pages using ISBN magic linksMbegu za lughaKamusiIsimu nenoKiarabukitabulughaKiswahilialfabetimaanaufafanuzimanenomuhtasarisarufividahizoalfabeti Kamusi Kutoka Wikipedia, kamusi elezo huru Jump to navigation Jump to search Kamusi za Kiswahili Kamusi ya Kiswahili-Kiswahili (kamusi wahidiya) Kamusi ya Kiswahili - Kiingereza (kamusi thania) Kamusi (kutoka neno la Kiarabu قاموس qamus ) ni kitabu ambacho kinaorodhesha maneno ya lugha yaliyokusanywa kutoka kwa watumiaji wa lugha fulani, kwa mfano lugha ya Kiswahili. Maneno hupangwa kwa mtindo wa alfabeti, hutoa maana, asili na ufafanuzi wa utumizi wa maneno na jinsi yanavyotumika au yanavyotamkwa. [1] Yaliyomo 1 Aina za kamusi 2 Muundo wa kamusi 2.1 1. Utangulizi wa kamusi 2.2 2. Matini ya kamusi 2.3 3. Sherehe ya kamusi 3 Faida za kamusi 4 Dhima ya picha katika kamusi 5 Marejeo 6 Tazama pia 7 Viungo vya nje Aina za kamusi | Kuna aina mbalimbali za kamusi ambazo ni: Kamusi wahidiya yaani ya l
Read more

Swift 4 - func physicsWorld not invoked on collision? The Next CEO of Stack OverflowHow to call Objective-C code from Swift#ifdef replacement in the Swift language@selector() in Swift?#pragma mark in Swift?Swift for loop: for index, element in array?dispatch_after - GCD in Swift?Swift Beta performance: sorting arraysSplit a String into an array in Swift?The use of Swift 3 @objc inference in Swift 4 mode is deprecated?How to optimize UITableViewCell, because my UITableView lags

Image
Strange use of "whether ... than ..." in official text Could a dragon use its wings to swim? Car headlights in a world without electricity How to avoid supervisors with prejudiced views? Is the offspring between a demon and a celestial possible? If so what is it called and is it in a book somewhere? Can I board the first leg of the flight without having final country's PR card? subequations: How to continue numbering within subequation? How do you define an element with an ID attribute using LWC? Relevant Part for a Badminton Serve What CSS properties can the br tag have? What are the unusually-enlarged wing sections on this P-38 Lightning? Why did early computer designers eschew integers? Is it a bad idea to plug the other end of ESD strap to wall ground? Magento 1.9 observer on only new product added ( no updated product ) Airplane gently rocking its wings during whole flight It is correct to match light sources with the same color temperat
Read more

Access current req object everywhere in Node.js ExpressWhy are global variables considered bad practice? (node.js)Using req & res across functionsHow do I get the path to the current script with Node.js?What is Node.js' Connect, Express and “middleware”?Node.js w/ express error handling in callbackHow to access the GET parameters after “?” in Express?Modify Node.js req object parametersAccess “app” variable inside of ExpressJS/ConnectJS middleware?Node.js Express app - request objectAngular Http Module considered middleware?Session variables in ExpressJSAdd properties to the req object in expressjs with Typescript

Image
What steps should I take to lawfully visit the United States as a tourist immediately after visiting on a B-1 visa? Would dual wielding daggers be a viable choice for a covert bodyguard? For a hashing function like MD5, how similar can two plaintext strings be and still generate the same hash? Using Newton's shell theorem to accelerate a spaceship Why doesn't sea level show seasonality? What prevents someone from claiming to be the murderer in order to get the real murderer off? Has anyone in space seen or photographed a simple laser pointer from Earth? What does (void *)1 mean Why does the U.S. tolerate foreign influence from Saudi Arabia and Israel on its domestic policies while not tolerating that from China or Russia? Modulus Operandi What's the point of having a RAID 1 configuration over incremental backups to a secondary drive? How to ask for a LinkedIn endorsement? Good resources for solving techniques (Metaheuristics, MILP, CP etc) How woul
Read more