Fill cell value down until a new cell value occurs - VBA script for Microsoft Excel

msexcel_fill_values_down.vba

' VBA script to take a value in column A and fill it down until a new value shows up in a cell for Microsoft Excel
' Author: Al Chen ([email protected])
' Last Updated: September 6th, 2020
' Video tutorial: https://youtu.be/t-32QkyjKVE?t=1109

Sub fillValuesDown()
  Dim lastRow As Double
  lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
  Dim currentRange As Variant: Set currentRange = ActiveSheet.Range("A2:A" & lastRow)
  ReDim newRange(1 To lastRow)
  Dim newFillValue As String
  Dim i As Long
  
  i = 1
  For Each cell In currentRange
     If IsEmpty(cell.Value) = False Then
       newFillValue = cell.Value
       newRange(i) = newFillValue
       i = i + 1
    Else
      newRange(i) = newFillValue
      i = i + 1
    End If
  Next cell
  currentRange.Value = Application.Transpose(newRange)
End Sub