2
\$\begingroup\$

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?

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

2 Answers 2

1
\$\begingroup\$

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
\$\endgroup\$
2
  • 4
    \$\begingroup\$ 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. \$\endgroup\$
    – pacmaninbw
    Jan 3, 2020 at 18:39
  • \$\begingroup\$ Wow, much fast than my way, Thanks! \$\endgroup\$ Jan 3, 2020 at 19:32
1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.