If cell is blank the copy the value above it.

[gheyman]

I have a table named "CMCS_BoM_MakeTable" In that table I have a Field/Column named "WBS"

My tab name is Material Ad Hoc MT

I need to go down the WBS column and if there is a cell I need to populate it with the value that's directly above it (the row above it). I want to use Column A (or "Task ID") to determine the Last Row.

Is this possible in VBA?

THANK YOU FOR YOUR TIME AND HELP.

PS there could be two consecutive cells that are blank - So I need the code to run from the top to the bottom so that it fills the first blank and then fills the next consecutive blank with the value that was filled with the one above it - so that I do not have any blanks in that row. Thank you!

 

[gheyman]

I am very proud of myself - lol

Sub FillBlankCells()

Dim LastRow As Long
Dim Cnt As Long
Dim Cnt1 As Long

Sheets("Material Ad Hoc MT").Select

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For Cnt = 1 To LastRow

Cnt1 = Cnt - 1

        If Cells(Cnt, 2).Value = "" Then
        Cells(Cnt, 2).Value = Cells(Cnt1, 2)
        End If
        
    Next Cnt

End Sub

But if someone sees a problem with this code or can show me a better way, please let me know.

 

[Joe4]

This:

For Cnt = 1 To LastRow

Cnt1 = Cnt - 1

        If Cells(Cnt, 2).Value = "" Then
        Cells(Cnt, 2).Value = Cells(Cnt1, 2)
        End If
        
    Next Cnt

could all be simplified to just this:

For Cnt = 1 To LastRow
    If Cells(Cnt, 2).Value = "" Then Cells(Cnt, 2).Value = Cells(Cnt-1, 2)
Next Cnt

 

[Norie]

Nothing wrong with the code you've posted.

You could do it without looping though.

Sub FillBlanks()
Dim tbl As ListObject

    Set tbl = Sheets("Material Ad Hoc MT").ListObjects("CMCS_BoM_MakeTable")

    With tbl.ListColumns("WBS").Range
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With

End Sub

 

[steve the fish]

Hi Norie. That would need error handling using special cells in case there are none.

 

[gheyman]

Thank you all. This is very educational.