Optimizing VBA Code for If Then Statement with Formula

[vgresia]

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 removing 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

 

[Michael M]

Is there more than 1 comma in the cell ?
Is the formula the same all the way down col J ?

 

[vgresia]

Hi Michael - there are potentially more than 1 comma in the cell. For some reason some values will end in a comma and I want to delete that extra comma. The formula is being applied only to the empty cells in column J. There is another formula in a different macro that gets applied to calculate values in column J, and those values then get hardcoded into column J. Hope that makes sense!

 

[MARK858]

If you put the formula below in an empty cell, changing the J2 to a cell that is blank does it give TRUE or FALSE?

=ISBLANK(J2)

 

[vgresia]

If J2 is a blank cell, it says TRUE.

 

[Michael M]

Try this for the fill blanks

Sub MM1()
Dim r As Range, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
        With Range("J2:J" & lr).SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=IFERROR((IF(LEFT(RC[-9],6)=""master"", get_areas(RC[-7]), """")),"""")"
        End With
End Sub

CAN ALL the commas be removed from each cell ??

 

[vgresia]

Thank you! I tried this code and received a Run Time Error 1004 saying that no cells were found. Unfortunately, all of the commas cannot be deleted.

 

[Michael M]

This should eliminate that problem

Sub MM2()
Dim r As Range, lr As Long, r1 As Range, r2 As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set r1 = Cells.SpecialCells(xlCellTypeBlanks)
Set r2 = Range("J2:J" & lr)
    If Intersect(r1, r2) Is Nothing Then
    Else
        With Range("J2:J" & lr).SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=IFERROR((IF(LEFT(RC[-9],6)=""master"", get_areas(RC[-7]), """")),"""")"
        End With
    End If
End Sub

 

[MARK858]

Thank you! I tried this code and received a Run Time Error 1004 saying that no cells were found. Unfortunately, all of the commas cannot be deleted.

Are you sure that you changed the cell in the formula to blank cells address when I asked the below question as you shouldn't get that error if you got TRUE as a result for any of your blank cells in the range?

If you put the formula below in an empty cell, changing the J2 to a cell that is blank does it give TRUE or FALSE?

=ISBLANK(J2)

If J2 is a blank cell, it says TRUE.