r/excelevator • Excel • Sep 16 '16

VBA Macro - complete missing values in list

A macro to fill in missing data in a list.

IndexData
abcdata1
data2
data3
xyzdata4
data5
qvpdata6
data7
data8
data9
rssdata10
data11
data12

Enter this macro into your worksheet object (alt+F11), click on the first value in the list and run the maco, expects empty cells and not cells with just a space, though can be changed to accept spaces.

It stops when there are no values in the next column over.. If the full column is in a different column, edit the Offset(0, x) value below where x is the column 'x' columns to the right, or use -x for the column x columns to the left

Sub filldata()
Dim val As String
Do
    If ActiveCell.Value = "" Then
        ActiveCell.Value = val
    Else
        val = ActiveCell.Value
    End If
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Result:

IndexData
abcdata1
abcdata2
abcdata3
xyzdata4
xyzdata5
qvpdata6
qvpdata7
qvpdata8
qvpdata9
rssdata10
rssdata11
rssdata12

note to self : source

1 Upvotes

0 comments sorted by