Macro gradually uses up all of my memory and slows down

[sjp22]

Hello

I have written this macro which I run about 1000 times in a loop. It runs solver and copies the results to a row in my spreadsheet so i end up with a sheet of solver results.
The problem is that every time it runs it eats up memory and slows down. I timed it and the first 100 runs take about 2 minutes and the last 100 takes about 15 minutes, the ram use increases by about 4gb in that time too.

As this is the first VBA code I have written and I have been learning as I go along, I assume its something I have done wrong in the code. Does anyone know if there is anything in it that might be causing my problem?

Thanks


Sub run()
'
' run Macro
' run solver
'
' Ueyboard Shortcut: Ctrl+n
'
Application.ScreenUpdating = False

'Pastes values so they dont update as solver trys to work on them

Range("B4:T13").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Set rRange = Nothing

' runs solver

SolverOk SetCell:="$W$20", MaxMinVal:=2, ValueOf:=0, ByChange:=Range("$B$23")
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1, ReportArray:=Array(1)

' pastes the results into a row

Sheets("Answer Report 1").Select
Range("E16").Select
Selection.Copy
Sheets("Mixmod").Select
Range("M26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Answer Report 1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete

Range("A1").Select
Range("B26:N26").Select
Selection.Copy
Range("B27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set rRange = Nothing

'moves the rows of results down to prepare for the next run

Rows("27:27").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' puts the formulas back into the cells which has values pasted at the beginning

Range("U4:U13").Select
Selection.AUTOFILL Destination:=Range("B4:U13"), Type:=xlFillDefault
Range("B4:T13").Select
Set rRange = Nothing
ActiveWindow.SmallScroll Down:=42
Rows("150:150").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-54
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 1
Application.CutCopyMode = False

End Sub

 

[chuckchuckit]

Seems to be some header code missing, rRange and Array??

Soething is allocating memory and not being released it appears, but we need to know what global variables you may have.

 

[sjp22]

The "Set rRange = Nothing" part is just code i was trying to find out if it made it fix the memory issue. I dont think it actually does anything.

What do you mean by global variables?