CODEWITHSUNDEEP
excelvbaif-statementoptimization
Victoria G
Victoria G

Reputation: 81

Speeding Up VBA Code for If Then Statement with Formula

I currently have two if then VBA codes, that are running extremely slow for my large data set and am looking for ways to optimize and speed them up.

The first formula is looking in a range of cells in column J that have a value in column A, and if they are blank in J then entering in a formula that contains a user defined function.

The second code is looking to see if any of the values in column J end in a , and if they do then remove that comma. Any help would be greatly appreciated! 

Sub FillEmpty()
    Dim r As Range, LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).row
    For Each r In Range("J2:J" & LastRow)
        If r.Text = "" Then r.FormulaR1C1 = _
           "=IFERROR((IF(LEFT(RC[-9],6)=""master"", get_areas(RC[-7]), """")),"""")"
    Next r
End Sub

Sub NoComma()
    Dim c As Range
    For Each c In Range("J:J")
        With c
            If Right(.Value, 1) = "," Then .Value = Left(.Value, Len(.Value) - 1)
        End With
    Next c
End Sub

Upvotes: 1

Views: 139

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Speedup: Arrays

1. Code

It is unbelievable that you do not need formulaR1C1 to get a formula into the range when pasting from the array into range. But it's working on my computer. To conclude, the same principle from the second code is applied on the first: Range into Array, Loop and Array into Range. It doesn't get faster than this. The other idea for the first code was to create a range union and then paste the formula in one go.

Sub FillEmpty()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim vntFE As Variant             ' Range Array
  Dim i As Long                    ' Range Array Rows Counter

  ' Paste range into array.
  vntFE = Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1)

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntFE)
    If vntFE(i, 1) = "" Then vntFE(i, 1) = "=IFERROR((IF(LEFT(RC[-9],6)" _
        & "=""master"", get_areas(RC[-7]), """")),"""")"
  Next

  ' Paste array into range.
  Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1) = vntFE

End Sub

Sub FillEmptyEasy()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim rng As Range                 ' Range
  Dim vntFE As Variant             ' Range Array
  Dim LastRow As Long              ' Last Row
  Dim i As Long                    ' Range Array Rows Counter

  ' Calculate Last Row.
  LastRow = Cells(Rows.Count, cCol).End(xlUp).Row
  ' Calculate Range.
  Set rng = Cells(cFirst, cCol).Resize(LastRow - cFirst + 1)
  ' Paste range into array.
  vntFE = rng

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntFE)
    If vntFE(i, 1) = "" Then vntFE(i, 1) = "=IFERROR((IF(LEFT(RC[-9],6)" _
        & "=""master"", get_areas(RC[-7]), """")),"""")"
  Next

  ' Paste array into range.
  rng = vntFE

End Sub

2. Code

Sub NoComma()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim vntNoC As Variant            ' Range Array
  Dim i As Long                    ' Range Array Rows Counter

  ' Paste range into array.
  vntNoC = Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1)

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntNoC)
    If Right(vntNoC(i, 1), 1) = "," Then _
        vntNoC(i, 1) = Left(vntNoC(i, 1), Len(vntNoC(i, 1)) - 1)
  Next

  ' Paste array into range.
  Cells(cFirst, cCol).Resize(Cells(Rows.Count, cCol) _
      .End(xlUp).Row - cFirst + 1) = vntNoC

End Sub


Sub NoCommaEasy()

  Const cCol As Variant = "J"      ' Column Letter/Number
  Const cFirst As Long = 2         ' First Row

  Dim rng As Range                 ' Range
  Dim vntNoC As Variant            ' Range Array
  Dim lastrow As Long              ' Last Row
  Dim i As Long                    ' Range Array Rows Counter

  ' Calculate Last Row.
  lastrow = Cells(Rows.Count, cCol).End(xlUp).Row
  ' Calculate Range.
  Set rng = Cells(cFirst, cCol).Resize(lastrow - cFirst + 1)
  ' Paste range into array.
  vntNoC = rng

  ' Loop through array and perform calculation.
  For i = 1 To UBound(vntNoC)
    If Right(vntNoC(i, 1), 1) = "," Then _
        vntNoC(i, 1) = Left(vntNoC(i, 1), Len(vntNoC(i, 1)) - 1)
  Next

  ' Paste array into range.
  rng = vntNoC

End Sub

Upvotes: 2

Related Questions