'Update blank cells in a column based on fixed match criteria

I have some blank cells within the range of P4:R22. Based on matching information with preceding cell in column X (range X4:X22). I wan't to update the information in the blank cells if the cell information in column X do not match in that case it will be updated with "--" information. Can anyone update the code?

Sub Blank_Update()

Dim tempRange As Range, tempArray As Variant, rowStart As Long, rowEnd As Long, lastRow As Long, lastCol As Long
Dim i As Long, j As Long, tempValue As Variant

' The assumption is that we are starting in row 2, and go as far down as there are cells in Column A
' Also that we are using Column A as a reference.
' So we start by getting this range and assigning it to our variable.
lastRow = Range("P" & ActiveSheet.Rows.Count).End(xlUp).Row
lastCol = ActiveSheet.UsedRange.Columns.Count
Set tempRange = Intersect(ActiveSheet.UsedRange, Range("P4:P" & lastRow).EntireRow)

' We are going to assume that we are not concerned about pasting formats etc.
'(If we are concerned with that, we would need to change our code)
'Set the tempArray to be this range that we acquired above.
tempArray = tempRange.Value

rowStart = 1
While rowStart <= lastRow

    rowEnd = rowStart

    ' First get the rows we are going to be looking at
    ' Keep iterating rowEnd until we find a new value, or we reach the end
    While tempArray(rowEnd, 1) = tempArray(rowStart, 1) And rowEnd < lastRow
        rowEnd = rowEnd + 1
    Wend
    ' If we did reach a new value, go back one to get the real row range.
    If Not tempArray(rowEnd, 1) = tempArray(rowStart, 1) Then rowEnd = rowEnd - 1

    ' Now that we have a range, we loop over the row range and column range.

    ' For each column
    For j = 2 To lastCol

        ' Cycle through the rows to find an acceptable value
        tempValue = ""
        For i = rowStart To rowEnd
            If Not Len(tempArray(i, j)) = 0 Then tempValue = tempArray(i, j): Exit For
        Next i

        ' If we found a value, populate the whole section accordingly
        If Not Len(tempValue) = 0 Then
            For i = rowStart To rowEnd
                tempArray(i, j) = tempValue
            Next i
        End If

    Next j

    ' After we did this for each column, we now need to iterate to the next section
    rowStart = rowEnd + 1

Wend

  ' Finally we put the new data back into the sheet
    tempRange = tempArray

    ' And clear the variables
    Set tempRange = Nothing: Set tempArray = Nothing


    End Sub

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source