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