add additional columns to this macro

[steve400243]

Hello, I use this VBA to fill blanks in column D. I need Column's B, C, and G to also have the blanks fill

Sub Fill_Blanks()
  With Range("D3:D" & ActiveSheet.UsedRange.Rows.Count)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[Joe4]

Try this:

Sub Fill_Blanks()

    Dim cols()
    Dim i As Long
    Dim lr As Long
    Dim rng As Range
   
'   Set columns to update
    cols = Array("B", "C", "D", "G")
   
    Application.ScreenUpdating = False
   
'   Find last row on sheet with data
    lr = ActiveSheet.UsedRange.Rows.Count
   
'   Loop through columns
    For i = LBound(cols) To UBound(cols)
'       Build range
        Set rng = Range(Cells(3, cols(i)), Cells(lr, cols(i)))
'       Fill blanks
        On Error Resume Next
        With rng
            .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
            .Value = .Value
        End With
        On Error GoTo 0
    Next i
   
    Application.ScreenUpdating = True
   
End Sub

 

[steve400243]

Try this:

Sub Fill_Blanks()

    Dim cols()
    Dim i As Long
    Dim lr As Long
    Dim rng As Range
  
'   Set columns to update
    cols = Array("B", "C", "D", "G")
  
    Application.ScreenUpdating = False
  
'   Find last row on sheet with data
    lr = ActiveSheet.UsedRange.Rows.Count
  
'   Loop through columns
    For i = LBound(cols) To UBound(cols)
'       Build range
        Set rng = Range(Cells(3, cols(i)), Cells(lr, cols(i)))
'       Fill blanks
        On Error Resume Next
        With rng
            .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
            .Value = .Value
        End With
        On Error GoTo 0
    Next i
  
    Application.ScreenUpdating = True
  
End Sub

Excellent, Thank you for providing this. I appreciate your time.

 

[Joe4]

You are welcome.
Glad I was able to help.

And if you ever want to add or remove columns, all you have to do is edit the values in the array (don't need to add any lines of code, just edit that one that lists all the columns to include).

 

[steve400243]

You are welcome.
Glad I was able to help.

And if you ever want to add or remove columns, all you have to do is edit the values in the array (don't need to add any lines of code, just edit that one that lists all the columns to include).

Thank you, was just thinking about that.