'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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

