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?