Excel VBA: Last Non-Empty Fill

Sub LastNonEmptyFill()

With ActiveSheet.UsedRange

LastRow = .Rows(.Rows.Count).Offset(1, 0).Row

End With

Cells(LastRow, 2).Value = "END"

Dim c As Range, lv As Variant

lv = "NA"

For Each c In Worksheets("sheet1").Range("B1", Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp))

If c.Value <> "" Then

lv = c.Value

Else

c.Value = lv

End If

Next c

On Error Resume Next

Columns("A").SpecialCells(xlBlanks).EntireRow.Delete

End Sub

Sample

A B

Cat 1

2

Dog 1

2

3

Mouse 1

2

Result

A B

Cat 1

Cat 2

Dog 1

Dog 2

Dog 3

Mouse 1

Mouse 2