Fill cells with the contents of the next non-blank cell above

[ellison]

Hi, trying to (repeatedly!) fill cells with the contents of the next non-blank cell above.
And we can see how to go through the standard procedure of highlighting the cells, then: Home / Find & Select / Go To Special / Fill in Blanks / Enter Formula / Hit Ctrl Enter
But between fat fingers and having to repeat this over and over, we are seeing some mistakes.
Is there some code or another way of doing this to give us the result we are trying to aim at?

Hopefully these 2 mini sheets show V1 with blanks cells throughout. And V2 showing how we would like it to end up...

fill-from-next-none-blank-cell-above-question.xlsm
	A	B
1	Row	V1
2	2
3	3	1
4	4	fill
5	5
6	6
7	7	in
8	8
9	9
10	10
11	11
12	12	the
13	13	blanks
14	14
15	15
Sheet1

fill-from-next-none-blank-cell-above-question.xlsm
	A	B
1	Row	V2 with blanks filled in
2	2
3	3	1
4	4	fill
5	5	fill
6	6	fill
7	7	in
8	8	in
9	9	in
10	10	in
11	11	in
12	12	the
13	13	blanks
14	14	blanks
15	15	blanks
Sheet2

Thanks for taking a look!

 

[kevin9999]

Here's one way (run while the sheet is active)

Sub FillMeUp()
    Dim c As Range
    For Each c In Range("B3:B" & Cells(Rows.Count, "A").End(xlUp).Row)
        If c = "" Then c.FillDown
    Next c
End Sub

 

[ellison]

That works beautifully, HUGE thanks!!!

 

[kevin9999]

That works beautifully, HUGE thanks!!!

Happy to help, and thanks for the feedback

 

[ellison]

Actually Kevin, really sorry - I've just plugged this onto a sheet with a 4K rows and it's gone really slow...
Literally nothing else on the sheet apart from the 2 columns of data.
I was just wondering if there may be a tweak that can be done?!

 

[kevin9999]

Tested the following on 14K rows of data based on your OP. Took 0.06 secs. Change the sheet name to suit.

Option Explicit
Sub FasterFill()
    Dim ws As Worksheet, a, b, i As Long, s As String
    Set ws = Worksheets("Sheet1")                   '<-- *** Change to actual sheet name ***
    a = ws.Range("B3:B" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    For i = 1 To UBound(a, 1)
        If a(i, 1) <> "" Then
            s = a(i, 1)
            b(i, 1) = s
        Else
            b(i, 1) = s
        End If
    Next i
    ws.Range("B3").Resize(UBound(b, 1), 1).Value = b
End Sub

 

[ellison]

Brilliant, will give that a go - thanks again

 

[kevin9999]

Actually, I could have used slightly less code:

Sub FasterFill_V2()
    Dim ws As Worksheet, a, i As Long, s As String
    Set ws = Worksheets("Sheet1")                   '<-- *** Change to actual sheet name ***
    a = ws.Range("B3:B" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    For i = 1 To UBound(a, 1)
        If a(i, 1) <> "" Then s = a(i, 1)
        b(i, 1) = s
    Next i
    ws.Range("B3").Resize(UBound(b, 1), 1).Value = b
End Sub

 

[ellison]

That’s excellent - really appreciated!!

 

[jamiguel77]

what happend when depend of 2 values mi case:
actually:

Expected result:

the rows 11 and 12 not was filled because the combination initially dont have any value in column C

Thanks