VBA Last Row formula crashing excel on larger datasets

[Samsonite]

I am using the code below to copy a formula down to the last row in the active sheet. Then copy paste the values.

When running the macro on a sheet where the active range is small I am not having any issues. However, when I run the macro on a sheet with 66 columns and 900,000 rows Excel crashes. This was working on the larger dataset until a few days ago and I did not change anything.

I tried disabling COM add-ins and running in safe mode, however, I am getting the same results. Runs fine on smaller sheets, crashes on larger sheets.

When I step through the macro, it crashes at the line in bold below. Is there a maximum ActiveSheet.Cells range size that is causing issues?

Thanks for taking a look!



Sub CopyPasteFormulas()


Dim MyRow
Dim MyCol
Dim lastRow As Long


Application.Calculation = xlManual


MyRow = ActiveCell.Row
MyCol = ActiveCell.Column


'FIND method to determine Last Row with Data, in a worksheet


Dim rng As Range


Set rng = ActiveSheet.Cells
lastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row




Range(Cells(2, MyCol), Cells(2, MyCol)).Copy Range(Cells(2, MyCol), Cells(lastRow, MyCol))






ActiveSheet.Calculate


Range(Cells(5, MyCol), Cells(lastRow, MyCol)).Value = Range(Cells(5, MyCol), Cells(lastRow, MyCol)).Value




Application.Calculation = xlAutomatic




End Sub

 

[NdNoviceHlp]

Hi Samsonite and Welcome to the Board! I'm guessing your code is actually crashing on the line following the code U bolded (XL sometimes highlights the last line of code that worked before the error). Anyways, that's a lot of stuff to put on the clipboard... it's unreliable and will crash. Also, "find" is slow. U could trial something like this...

Dim Lastrow As Integer, MyCol As Integer
MyCol = ActiveCell.Column
With ActiveSheet
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
'Range(Cells(2, MyCol), Cells(2, MyCol)).Copy Range(Cells(2, MyCol), Cells(Lastrow, MyCol))
.Range(.Cells(2, MyCol), .Cells(Lastrow, MyCol)) = .Range(.Cells(2, MyCol), .Cells(2, MyCol))
End With

You could also use an array based solution. HTH. Dave
ps. Please use code tags.

 

[Samsonite]

Hi NdNoviceHlp

I am new to the forum. I'll be sure to use code tags.

Thanks for your help, but this does not quite work for my needs

I need the code to do the following:
1. find the last row with data in any column in the active sheet; I would like to ignore formatted or blanks
2. copy formula from row 2 of active column into all rows of that active column up to the last row of the active sheet
3. re-calculate; each row will have a unique value
4. copy paste values from row 5 through the last column of the active column; pasting values so that it does not recalculate 800k row each time I filter the data; leaving original formula in rows 2 through 4 since I will be modifying and refreshing this as the data changes.

Your code is not consistently returning the last row. The first time, I ran it, the last row was correct, however, after adding new data in a lower row, the macro did not return the new correct last row. Also, your code is pasting the value from row 2 into every row of that column. Since each row in my data will have a different value, I need the calculation to run and refresh first.

Also, I am pretty sure it is crashing on the lastrow step. I am stepping through the using F8 in the VBA editor. The bolded code is highlighted yellow and I press F8. Calculations run for a bit, then it crashes. Keep in mind this was working a few days ago with no problems. It also works without any problems on sheets with fewer rows and columns.

I'm not sure what an array based solution is, could you give an example?

Thanks

 

[NdNoviceHlp]

That code was just a general outline as I really didn't know what your outcome objective was. I hate using active sheet and/or active cell code. Anyways, some questions...
1) Last row of the active sheet or last row of the active column? I'll go with active sheet.
2) Copy formula from row 2 of active column to all rows of active column(1 to lastrow). Autofill formula? I'm guessing yes.
3) XL should calculate on its' own
4) Nor real sure I understand. Let's do 1-3 first.

Private Sub Test()
Dim Lastrow As Integer, MyCol As Integer, R As Range
MyCol = ActiveCell.Column
Set R = ActiveSheet.UsedRange
Lastrow = R.Rows.Count + R.Row - 1
With ActiveSheet
.Range(.Cells(1, MyCol), .Cells(Lastrow, MyCol)).FormulaR1C1 = .Cells(2, MyCol).FormulaR1C1
End With
End Sub

Dave