When I run my code, I get an "Excel not responding" error at this sub as I step through the code, more specifically the
write portion of this code. Can anyone help optimize this? I think this is because the loop is writing so many records cell by cell (50k) records and it's causing my Excel sheet to become unresponsive.
'writes the nRows and nCols from arr to startCell 'if fromTop is true it starts reading from the first element of arr, otherwise it starts fromm the last element Public Sub writeArrToWS(arr() As Variant, startCell As Range, fromTop As Boolean, nRows As Long, nCols As Long) Dim i As Long, j As Long, startRow As Long, startCol As Long Dim thisWS As Worksheet Dim writeVal As Variant Set thisWS = startCell.Worksheet startRow = startCell.row startCol = startCell.Column 'clear For i = 1 To nRows For j = 1 To nCols thisWS.Cells(startRow + i - 1, startCol + j - 1).value = "" Next j Next i 'write For i = 1 To WorksheetFunction.Min(nRows, UBound(arr, 1)) For j = 1 To nCols If fromTop Then writeVal = arr(i, j) Else writeVal = arr(UBound(arr, 1) - i + 1, j) thisWS.Cells(startRow + i - 1, startCol + j - 1).value = writeVal Next j Next i End Sub