Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel memory error when trying to run the macro

0

When the below macro is ran, the memory unavailable error occurs causing it to terminate the execution abruptly.

Below code is the replica of the manual steps that we must do in order to preserve the conditional formatting formulas for arrow for Trend Analysis.

Please suggest a work around to this issue

wsh.Cells(2, col).EntireColumn.Insert
wsh.Cells(2, col).EntireColumn.Value = wsh.Cells(2, col + 1).EntireColumn.Value
wsh.Cells(2, col).EntireColumn.Hidden = True
wsh.Cells(2, col + 1).EntireColumn.Value = wsh.Cells(2, col + 2).EntireColumn.Value

Or

'Columns(col).Select
'Cells(2, col).EntireColumn.Select
'Selection.Copy
'Selection.Insert Shift:=xlToRight
'Cells(2, col).EntireColumn.Hidden = True
'Cells(3, col + 2).EntireColumn.Copy Cells(3, col + 1).EntireColumn
excel
MS Office Version: 2016
posted by: Dr Liss (rep: 26) on Tue Aug 30, 2022 at 10:09 am
Answer
Discuss

Discussion

Dr Liss

Please attach another sample (data) file to your answer (to get values from workbook wb).
John_Ru (rep: 6142) Aug 30, '22 at 11:53 am
Hi,
I have attached the wb source file. Thanks
Dr Liss (rep: 26) Aug 30, '22 at 12:16 pm
Thanks for uploading the Source Sample file. When I saved it and put the full file path in B2 then stepped through the macro (with Day = 1), it filtered to no visible records and failed.

When I commented out the .AutoFilter line, it performed the first insert/value/hide lines using your (first) example code above (without a memory fail) but failed on the line in bold:
Dim i As Double
Set r = .Range("I2", .Range("I" & .Rows.Count).End(xlUp)) 'I
i = WorksheetFunction.Average(r.SpecialCells(xlCellTypeVisible))

Sorry but you didn't say which iteration of those lines cause the fail and I don't have time to guess or work out how this macro works so can't help you today.
John_Ru (rep: 6142) Aug 30, '22 at 12:36 pm
Add to Discussion

Answers

0

Thanks for your time John_Ru.

I encountered the error at the second chunk that is following the "Fails" comment but in the below line instead.

wsh.Cells(2, col).EntireColumn.Insert
posted by: Cartus (rep: 2) on Tue Aug 30, 2022 at 12:57 pm
Discuss

Discussion

Okay Cartus but Answers ought to be solutions- your comment would be better in the Discussion section of the Question (or - if you are Dr. Liss too - added to the Question to clarify).

I might take a second look tomorrow (my time zone ) 
John_Ru (rep: 6142) Aug 30, '22 at 2:30 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login