Excel VBE code find and replace

Question

I need to write a VBA code routine with an efficient way to replace blank cells with values from another cell. For example, my worksheet has some blank cells on random rows in column D. I want to replace these with the value on the same row in column A. In other words, go down all the rows and if column D is blank, replace it with column A.

I wrote some code that "walks" down every row of the worksheet looking for blanks in column D and copying the values from A into D, but it's ridiculously slow (some of these worksheets have more than half a million rows!)

 .Range("D1", Range("D1048576").End(xlUp)).Select
            Set A = XL.Selection
                For Each B In A.Rows
                        If Len(B.Value & vbNullString) = 0 Then
                            B.FormulaR1C1 = "=RC[-3]"
                        End If
                    DoEvents
                Next

Any suggestions?

Answer 1

try this code. It assumes that the worksheet with data is active.

Sub ReplaceCodeReview235023()
    With ActiveSheet.Columns("D")
       .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-3]"
    End With
End Sub

Answer 2

It is good to use offset and find empty cells just using (.Value = ""). Declaring all variables will also help you at debugging. If this will become a habit, it may be helpful in case of more complicated algorithms. No need to select anything for such an iteration. If you use VBA, why using formulas which will increase the workbook size?

Dim sh As Worksheet, rng As Range, cel As Range, lastRow As Long
  Set sh = ActiveSheet 'change it with your necessary sheet
   lastRow = sh.Cells(sh.Rows.count, "D").End(xlUp).Row
   Set rng = sh.Range("D1:D" & lastRow)
        For Each cel In rng
            If cel.Value = "" Then cel.Value = cel.Offset(0, -3)
        Next