Automatically update blank cells in a range with preceding cell values-CodePudding

I want to update some blank cell with preceding values in a range of P4 to P22, Q4 to Q22 & R4 t R22. In my case I have values in P4, Q4 & R4 if the cells below are blank and if corresponding column values of X4 matches with the corresponding blank cell values in X5 then the blank cells will automatically get updated. If it doesn't natch then the cells will stay blank. Can some one share a VBA based solution in excel?

CodePudding user response：

Let me show you, using some screenshots, how I handle this (you can record it into a VBA macro):

Start with a sheet with some data:

Select the range I want to work with:

Press Ctrl G (Goto), and choose "Special". In there, choose "Blanks":

Start typing a formula in the formula bar (you might need to press F2 for focusing on the formula bar), and type =B3 (the upper left value):

Press Ctrl ENTER (don't forget the Ctrl button):

Voila:

Have fun!

CodePudding user response：

Next time please share code that you have tried so far so it will easier for you and for the one who answering. Here is quick code example I tried to write according to your instructions. It can be improved and adjusted, but it still should do the task required.

Sub UpdateCells()

Dim sh As Worksheet
Dim y As Long

Set sh = Sheets("Your Sheet Name")

For y = 5 To 22 'starting from 5 to 22 since you have asked for this. It can be changed to your liking

    If sh.Cells(y, "X") = sh.Cells(y - 1, "X") Then 'compares values in column X
        sh.Cells(y, "P").Value = sh.Cells(y - 1, "P").Value 'copies values from above if values in column X matches
        sh.Cells(y, "Q").Value = sh.Cells(y - 1, "Q").Value
        sh.Cells(y, "R").Value = sh.Cells(y - 1, "R").Value
    End If
Next y

End Sub