How to clear memory to prevent “out of memory error” in excel vba?

I’ve found a workaround. At first it seemed it would take up more time, but it actually makes everything work smoother and faster due to less swapping and more memory available. This is not a scientific approach and it needs some testing before it works.

In the code, make Excel save the workbook every now and then. I had to loop through a sheet with 360 000 lines and it choked badly. After every 10 000 I made the code save the workbook and now it works like a charm even on a 32-bit Excel.

If you start Task Manager at the same time you can see the memory utilization go down drastically after each save.

Related Posts:

  1. Object Required Error in excel VBA
  2. What are .NumberFormat Options In Excel VBA?
  3. Better way to find last used row
  4. VBA, if a string contains a certain letter
  5. Excel VBA Macro: User Defined Type Not Defined
  6. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  7. Select method of Range class failed via VBA
  8. Does VBA contain a comment block syntax?
  9. Why does “Paste Method of Worksheet class failed” occasionally occur?
  10. How to return a result from a VBA function
  11. Use VBA to Clear Immediate Window?
  12. VBA: Else without If Error
  13. Recommended IDE for VBA
  14. ByRef argument type mismatch in Excel VBA
  15. VBA: Convert Text to Number
  16. VBA Error: “Compile error: Expected End Sub”
  17. Error: “Could Not Find Installable ISAM”
  18. Loop without Do error
  19. Row count where data exists
  20. Test or check if sheet exists
  21. How to detect if user select cancel InputBox VBA Excel
  22. macro run-time error ‘9’: subscript out of range
  23. Why does my VBA code throw an “Invalid outside procedure” error?
  24. Excel Solver Password: what is it and where can it be acquired/bought?
  25. Compile error: Next without For || VBA
  26. Is there a way to crack the password on an Excel VBA Project?
  27. MS Excel Scatterplot converts Months to Numbers
  28. What does <> mean?
  29. Excel to Google Sheets – Error: Formula parse error
  30. Swap x and y axis without manually swapping values
  31. Excel: Calculate the frequency of a particular month in a column of date cells
  32. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  33. How to find a value in an excel column by vba code Cells.Find
  34. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  35. Insert picture into Excel cell
  36. What is a correct MIME type for .docx, .pptx, etc.?
  37. Is there Infinity in Spreadsheets?
  38. Declaring variables in Excel Cells
  39. How to loop in excel without VBA or macros?
  40. What is correct content-type for excel files?
  41. How do I get countifs to select all non-blank cells in Excel?
  42. What is the function of FormulaR1C1?
  43. What exactly is the function of Application.CutCopyMode property in Excel
  44. Excel – SUMIFS for multiple columns
  45. Workaround for “Formula omits adjacent cells”
  46. VBA Excel – Insert row below with same format including borders and frames
  47. check if a file is open in Python
  48. Select all the cells below current cell in excel
  49. Excel: Can I create a Conditional Formula based on the Color of a Cell?
  50. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  51. How do I open space-delimited file in Microsoft Excel?
  52. Scraping data from website using vba
  53. Using SUBSTITUTE with wildcard characters
  54. Change default ‘delimiters’ in excel
  55. Adding a newline character within a cell (CSV)
  56. VB: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)
  57. How to install Date Picker form control in Excel 2016
  58. How to install Date Picker form control in Excel 2016
  59. How to fill color in a cell in VBA?
  60. (Excel) Conditional Formatting based on Adjacent Cell Value
  61. Shortcut to Apply a Formula to an Entire Column in Excel
  62. Refreshing all the pivot tables in my excel workbook with a macro
  63. How to label scatterplot points by name?
  64. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  65. Average in VBA in Excel
  66. VBA Excel “Compile error: Object Required”
  67. Choosing paper size (NOT DEFAULT sizes) in excel vba
  68. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  69. Paste Special error 1004 PasteSpecial method of Range class failed
  70. Using Excel VBA to run SQL query
  71. VBA: Getting run-time 1004: Method ‘Range’ of object ‘_Worksheet’ failed when using cells
  72. Cannot run the macro… the macro may not be available in this workbook
  73. ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
  74. Docker error : no space left on device
  75. VBScript to open a dialog to select a filepath
  76. I get this error: “glibc detected”
  77. What does ** mean in C?
  78. Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support
  79. How to open an Excel file in C#?
  80. Difference between hive.tez.container.size and tez.task.resource.memory.mb
  81. How to Clear Office Clipboard with VBA
  82. Compare Dates in VBA
  83. Is there a way to exclude the content from the post variable to save on RAM usage?
  84. PHP Memory Limit vs. WP Memory Limit
  85. WP_MEMORY_LIMIT didn’t work in wp-config
  86. Best way to diagnose which plug-ins/theme uses the most memory?
  87. WordPress database becoming huge. How to analyze and optimize it? fear of running out of memory
  88. What is the most efficient way to search users by their display name?
  89. In big trouble – Allowed memory size of 268435456 bytes exhausted
  90. WordPress hit memory limit but not from the server
  91. PHP Warning: include(): Unable to allocate memory for pool
  92. HIgh CPU usage – Will using cache plugins help
  93. Memory consumption in w3-total-cache vs wp-super-cache? [closed]
  94. High memory usage in my theme, how to find out why?
  95. I/O Usage goes max. just after every new post published and WP site down
  96. Fatal error: Allowed memory size of bytes exhausted (tried to allocate 36 bytes)
  97. wp_insert_post memory consumption
  98. Allowed memory size of (…) exhausted
  99. What does Virtual memory size in top mean?
  100. Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 20480 bytes) in

Leave a Comment