I am writing a macro that copies a formula, pastes it over a range,
then copies that and pastes the values. Its working over 30,000 rows,
one column at a time. I also put a loop in to break the range up into
managable chunks based on how many characters the formula was(It would
crash often when attempting to do this all at once). Now this takes
way too long to run and is pretty unstable, is there a better way?

Here is the function that breaks up the range and copies, pastes,
copies, then pastes again. Thanks!

'Copies and pastes a formula passed as an argument (FormulaRangeName)
and pastes the value down the range(argument RangeName)

Private Sub UpdateInChunks(RangeName As String, FormulaRangeName As
String)

Dim n As Long
Dim r As Integer


Application.ScreenUpdating = False

'This If statement makes a rough approximation of complexity (by
length) and determines from that how many cells to process at a time
If Len(Sheet1.Range(FormulaRangeName).Formula) <= 30 Then

r = 100

ElseIf (30 < Len(Sheet1.Range(FormulaRangeName).Formula) <= 60)
Then

r = 80

ElseIf (60 < Len(Sheet1.Range(FormulaRangeName).Formula) <= 90)
Then

r = 60

Else

r = 40

End If



' loops through ranges of length 'r', coping and pasting the formula
then copying and pasting the values
For n = 0 To (Sheet1.Range(RangeName).Rows.Count) \ r

Sheet1.Range(FormulaRangeName).Copy

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).PasteSpecial
Paste:=xlPasteFormulas

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).Copy

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).PasteSpecial
Paste:=xlPasteValues


Next n

Application.ScreenUpdating = True


End Sub