Excel crashes after running specific macro

[LauraJane]

Hi!

Every time I run the following macro, Excel crashes. I've tried it on two different machines one with 365, and the other with Microsoft Office Professional Plus 2016. I've turned off add-ins, and copied and pasted the code from the workbook module to the personal file. The file I'm running the macro on is an xlsx file. When i step through the code, the macro works fine and does not crash. The data that the macro is working on has a count of 5,915,363 cells, and the data is in a table.

Sub ConvertFormulas()'
' ConvertFormulas Macro
'


Dim rng As Range, cell As Range
Set rng = Range("STARData")
    rng.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    For Each cell In rng
        If cell.Value = "x" Then
            cell.ClearContents
        End If
    Next cell
End Sub

I don't know what to try next.

 

[lrobbo314]

Not sure why your code would make it crash. Try the following code and see if it fixes the crashing issue. Either way, the code below should be faster since it's much quicker to work with data in an array than to be constantly reading from and writing to the worksheet.

Sub fxToValue()
Application.ScreenUpdating = False
Dim r As Range:         Set r = Range("STARTData")
Dim AR() As Variant:    AR = r.value


For i = LBound(AR) To UBound(AR)
    For j = LBound(AR, 2) To UBound(AR, 2)
        If AR(i, j) = "x" Then AR(i, j) = vbNullString
    Next j
Next i


r.value = AR
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Maybe another option...

Sub STARDataClr()
    Application.ScreenUpdating = False
    With Range("STARData")
        .Value = .Value
        .Replace "x", "#N/A", xlWhole, , True, False, False
        .SpecialCells(xlConstants, xlErrors).ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

Not sure how it will perform with a range of 5,915,363 cells though.

 

[LauraJane]

Irrobo314 - Your code worked beautifully - it ran in a matter of seconds. Thank you!

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[lrobbo314]

Not sure how it will perform with a range of 5,915,363 cells though.

Not particularly well. I tested it and it took a long while, ~10 mins on some test data. For a minute there I thought that they'd have to use yours because writing that much data from an array can sometimes result in errors, but I think that only that happens when your're using Application.Transpose(array). I was surprised to see that there was such a discrepancy. I wonder why the replace takes so much longer...