VBA Fill spotty blank Cells Columns A-D w/Data from above it (Code Included)

[LNG2013]

Hello,

I have spotty data in cells in Columns A-D. I want to fill all the empty cells in Columns A-D with the data that would be above the missing cells filling down, until you get the next data and then fill down for those.

I have code that will do it but I need to change the range to from end up.
Column E is fully filled out and that should be what is determined the end range.

Thank you in advance!

Sub Fill()

Set Worksheet = Worksheets("Report")
Set Dataset = Worksheet.Range("A2:D50") ' This is where I need to change the range. Number of rows can be determined from end up of column E

For i = 1 To Dataset.Columns.Count
    For j = 1 To Dataset.Rows.Count
        If Dataset.Cells(j, i) = "" Then
            Dataset.Cells(j, i) = Dataset.Cells(j - 1, i)
        End If
    Next j
Next i

End Sub

 

[Joe4]

Here is an easy way to do it with no loops:

Sub MyFillBlanks()
    Range("A2:D50").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("A2:D50").Value = Range("A2:D50").Value
End Sub

 

[LNG2013]

So sorry I may have been poor in my wording.

The Range A2:D50 is the issue A basically need it to be A2:D - D end up, but based on E up

 

[Joe4]

OK, try this:

Sub MyFillBlanks()
    Dim lr as Long
    lr = Cells(Rows.Count, "E").End(xlUp).Row
    Range("A2:D" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("A2:D" & lr).Value = Range("A2:D" & lr).Value
End Sub

 

[LNG2013]

OK, try this:

Sub MyFillBlanks()
    Dim lr as Long
    lr = Cells(Rows.Count, "E").End(xlUp).Row
    Range("A2:D" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("A2:D" & lr).Value = Range("A2:D" & lr).Value
End Sub

This worked thank you so very much!

 

[Joe4]

You are welcome.
Glad I was able to help!