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?

• Brian Battles: I set the xl application to Visible=False when this is running

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

• pacmaninbw: Welcome to code review, where we review code and provide insightful observations that can help the asker improve their code. Answers are required to make at least one insightful observation about the users code and can but generally don't include alternate solutions. Alternate solutions by themselves are considered poor answers and may be down voted and deleted.
• Brian Battles: Wow, much fast than my way, Thanks!

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