# Excel VBE code find and replace

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?

• I set the xl application to Visible=False when this is running Jan 3, 2020 at 19:07

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

• 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. Jan 3, 2020 at 18:39
• Wow, much fast than my way, Thanks! Jan 3, 2020 at 19:32

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