VBA – Fill Down Blanks

This is a common problem that many users have: they got a list of data, that has some blank cells in the middle, and they want to fill the blanks with the value from the last cell above. Here’s an example on how your sheet could look like:


As you can see, we have some blank cells that we need to fill with the value from the last filled row above on the Company and Country columns. We would like to obtain this:


For that, we can use this code:

Sub FillSelectionBlanksBelow()
    Dim Cell As Range
    For Each Cell In Selection
        If Cell.Value = "" Then Cell.Value = Cell.Offset(-1, 0).Value
    Next Cell
End Sub

Then you just need to select the range where you want to apply it (in this case A2:D11) and run the macro and you will get the blanks cells filled down.

4 comentários:

Evelyn said...

Incredibly helpful for someone who knows very little about VB - thank you so much!

Seamus Larrissey said...

Thanks so much for this... Had a 5000 line spreadsheet to fill blanks in.

Never fails to amaze me what other people can do in excel

Anonymous said...

Thank you!
There were many pages online with code that did not work for me, this one did and is so clean and simple!

Unknown said...

Hey there, just want to say thank you for sharing this. As noted above, very clean and it works great. I really appreciate it!