Fill in empty cells with adjacent cell value

[dystophic]

Good afternoon,

I was wondering if someone can help -

I have a column E containing cells of different values; some of those cells are blank. I also have an adjacent column D that doesn't have any blank values.

What I basically trying to do is use VBA fill in the empty cells in column E with the value of their adjacent column D cells. As below:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ745433[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]19180597/1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ746529[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ745000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]19178853/1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ750759[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
to [TABLE="width: 500, align: right"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ745433[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19180597/1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ746529[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ZJ746529[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ745000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9178853/1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]ZJ750759[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ZJ750759[/TD]
[/TR]
</tbody>[/TABLE]











Thank-you.

 

[wisewood]

I would create a new column with a formula in each cell.

=if(E2="",D2,E2)

Hide column E. Use the new column instead.

 

[Joe4]

Welcome to the Board!

This is pretty easy to do without the use of any additional columns or VBA.

You can use the first method explained here for populating blank cells with the value of the row above it. However, instead of writing your formula to populate it with the value of the cell above it, write the formula for the cell to the left of it.
https://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html

 

[dystophic]

Thank you for your suggestions!

Apologies, I haven't been very clear with my request. I need to do this specifically with the use of a macro in VBA.

Also sorry for the bad table formatting in the first post!

 

[Joe4]

One way is to turn on your Macro Recorder and perform the steps I pointed you to.

Here is a cleaned up version of that with comments added:

    Dim lastRow As Long

'   Find last row in column D with data
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Populate all blank cells in column E with formula
    Range("E1:E" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"

 

[Frank_Excel]

Sub CopyAdjacent()    
    Dim sh As Worksheet
    Dim rowCount As Long
    
    'Insert the worksheet name
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    'Count the row having data in the column D
    
    rowCount = sh.Range("D1", sh.Range("D1").End(xlDown)).Rows.Count


    For i = 2 To rowCount
    
    ' Fill empty cells of the column E with the adjacent cell value
    If IsEmpty(Cells(i, 5).Value) Then
        Cells(i, 5).Value = Cells(i, 4).Value
    End If
    
    Next i


End Sub

 

[Joe4]

Welcome to the Board Frank!

Loops will certainly work, but whenever possible, it is best to avoid using them. They tend to be a bit slow and memory hogs. For large files, the time lag can become quite pronounced.
Sometimes there isn't really another good option and you have to use loops (I do it all the time), but when there is, it is usually better to go the other route.

 

[dystophic]

That worked perfectly!

Really happy with the response time and solutions from all of you! Thanks a lot.

 

[Joe4]

You are welcome!

One last tip. If you want to change the formulas used to fill those blank cells with hard-coded values instead, just add this line to the end of the code I posted above:

Range("E1:E" & lastRow).Value = Range("E1:E" & lastRow).Value

 

[Frank_Excel]

Welcome to the Board Frank!

Thank you Joe4!!

Loops will certainly work, but whenever possible, it is best to avoid using them. They tend to be a bit slow and memory hogs. For large files, the time lag can become quite pronounced.
Sometimes there isn't really another good option and you have to use loops (I do it all the time), but when there is, it is usually better to go the other route.

Thanks for the clarification and for the suggestion!!! I will apply your tip in the future.