Avoid Memory Issue When running this macro?

[squeakums]

I'm running the below macro several times in a combined macro sheet - this moves columns once the data is imported. Is there a way to avoid getting that annoying memory error with massive macros or to rewrite the below? It works on other tabs but is erroring out on this one.


Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer

'Place the column headers in the end result order you want.
arrColOrder = Array("LOB Vendor Executive", "Vendor Manager", "Vendor Name", "Finding ID", "Risk Level", "Due Date", "Finding Status", "Question", "Region")



counter = 1

Application.ScreenUpdating = False



For ndx = LBound(arrColOrder) To UBound(arrColOrder)

Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If

Next ndx

 

[Norie]

Instead of cutting/inserting entire columns you could try only cutting/inserting rows with data.

Alternatively, and this might not be suitable, you could copy the columns, in the required order, to a new sheet.

That would avoid shifting large amounts of data around as you cut/insert.

 

[squeakums]

How would I write the macro only cut rows with data in it verses a2:a3000 etc?

 

[squeakums]

I found another way - if you just save the file while in the macro it'll wipe the memory and start a fresh and then it won't cause the issues.