Nov 06 2020 05:50 PM
Nov 06 2020 09:15 PM - edited Mar 10 2021 07:14 AM
@gty1992 This could be done as follows:
1) Select the range B2:B5000 (or whatever your exact range may be)
2) Press F5 (Go to, Special, Blanks)
3) This should select all blank cells in the range with the active cell in B3
4) Now, in =B2 and press Ctrl-Enter
Edit: 4) Now, enter =B2 and press Ctrl-Enter
5) All blank cells will now contain a formula pointing to the cell directly above it.
6) Copy column B and paste as values on top of itself if you don't want to keep the formulae.
Alternatively, use "Get & Transform Data" (a.k.a. Power Query "PQ") if this is recurring process on a data set that might be a bit more complex than what you described above. PQ has a standard function for "Fill Down"
Nov 07 2020 02:25 AM
I would like to suggest two possible methods, one is using Helper Data & Formula and second is VBA Macro.
Situation NAME column is blank.
How it works:
Private Sub CommandButton1_Click() For Each cell In Selection If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub
Macro will fill the blank cells as shown in Screen Shot above.
Mar 10 2021 07:13 AM
@Marra93 I think it contains a typo. It should have read:
"4) Now, enter =B2 and press Ctrl-Enter"
What I meant to write is that when you have selected all the blank cells (step 3) and the active cell is B3, enter the formula =B2 and then Ctrl-Enter.
Sorry about that.