Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fill cell value down until a new cell value occurs - VBA script for Microsoft Excel
' 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 (al@coda.io)
' 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment