VBA: changing data through a range vs. array-CodePudding

I am trying to make my macro run faster. My spreadsheet is 300,000 rows and as part of the macro I am trying to change the values of columns AF & AG to values that depend on the value of column A.

I did this using a loop function, but I heard that using an array would be faster. I switched to the array code below and the macro runs about 5 seconds longer. Is there a more efficient way to code to save time on this step?

Code 1

Dim cell As Range

For Each cell In Range("A2:A400000")
   If cell.Value = "N149933" _
   Or cell.value = "N103291" _
   Or cell.value = "NS11375" _
   Or cell.value = "N187037" _
   Or cell.value = "N132921" _
   Or cell.value = "NS03359" _
   Or cell.value = "N102301" Then
      cell.Offset(0,32).Value = "Foundation and Endowment (Charities)"
      cell.Offset(0,31).Value = "QP"
ElseIf cell.Value = "NS35481" Then
      cell.Offset(0,32).Value = "Corporate"
ElseIf cell.Value = "NS07301" Then
      cell.Offset(0,32).Value = "Public Fund (Government)"
      cell.Offset(0,31).Value = "QP"
ElseIf cell.value = "NS29802" _
    Or cell.value = "NS29803" _
    Or cell.value = "NS29801" Then
      cell.Offset(0,32).Value = "Insurance"
  Else: cell.Offset(0,32).Value = "High Net Worth Individuals"
  End If
Next Cell

Code 2

Dim arr as Variant
arr = Worksheets("Input").Range("A1").CurrentRegion

Dim i as Long
For i = LBound(arr,1)   1 To UBound(arr,1)
   If arr(i,1) = "N149933" _
   Or arr(i,1) = "N103291" _
   Or arr(i,1) = "NS11375" _
   Or arr(i,1) = "N187037" _
   Or arr(i,1) = "N132921" _
   Or arr(i,1) = "NS03359" _
   Or arr(i,1) = "N102301" Then
     arr(i,32) = "QP"
     arr(i,33) = "Foundation and Endowment (Charities)"
ElseIf arr(i,1) = "NS35481" Then
     arr(i,33) = "Corporate"
ElseIf arr(i,1) = "NS07301" Then
     arr(i,32) = "QP"
     arr(i,33) = "Public Fund (Government)"
ElseIf arr(i,1) = "NS29802" _
    Or arr(i,1) = "NS29803" _
    Or arr(i,1) = "NS29801" Then
     arr(i,33) = "Insurance"
Else: arr(i,33) = "High Net Worth Individuals"
EndIf
Next i

Worksheets("Input").Range("A1").CurrentRegion.ClearContents

Dim rowCount As Long, columnCount As Long
rowCount = UBound(arr,1)
columnCount = UBound(arr,2)
Worksheets("Input").Range("A1").Resize(rowCount,columnCount).Value = arr

CodePudding user response：

I suppose it is slower due to clearcontents, which you don't need as you don't change the size of the array. Furthermore you should use Application.screenupdating = false etc, see here https://www.spreadsheet1.com/how-to-optimize-vba-performance.html

CodePudding user response：

Perhaps not using ClearContents will speed things up.

Also, a Select Case might make the code more readable but probably won't have any affect on performance.

Dim rng As Range
Dim arr As Variant
Dim I As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set rng = Worksheets("Input").Range("A1").CurrentRegion
    arr = rng.Value

    For I = LBound(arr, 1)   1 To UBound(arr, 1)
        Select Case arr(I, 1)
            Case "N149933", "N103291", "NS11375", "N187037", "N132921", "NS03359", "N102301"
                arr(I, 32) = "QP"
                arr(I, 33) = "Foundation and Endowment (Charities)"
            Case "NS35481"
                arr(I, 33) = "Corporate"
            Case "NS07301"
                arr(I, 32) = "QP"
                arr(I, 33) = "Public Fund (Government)"
            Case "NS29802", "NS29803", "NS29801"
                arr(I, 33) = "Insurance"
            Case Else
                arr(I, 33) = "High Net Worth Individuals"
        End Select
    Next I

    rng.Value = arr
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic