Autofill when there are blank values

Question

Needing help with the following.

I want for the first column: To auto fill the remaining blank spaces until another value is found. Example: RMDSADMN would be autofilled until TXAADGLI is found, then this would be autofilled until TXAADM, then this would get filled one time since there is one blank space.

I tried adding input boxes where I had to manually insert the name of each value but I am aiming for something that automatically checks the values, instead of me inserting them.

Answer 1

Try this:

Sub test()

Dim lRow As Integer
Dim i As Integer

lRow = Cells(Rows.Count, 5).End(xlUp).Row

With ThisWorkbook.ActiveSheet

For i = 1 To lRow

If .Cells(i, 1).Value = "" Then

.Cells(i, 1).Value = .Cells(i - 1, 1).Value

End If

Next i

End With

End Sub

Answer 2

Try,

with activesheet
    with .cells(1,1).currentregion
        .specialcells(xlcelltypeblanks).formular1c1 = "=r[-1]c"
        .value = value
    end with
end with

Answer 3

The following code can help is there you need to auto-fill the previous values between 1st and last cells depending on value of 1st cell as mentioned in question Excel - VBA fill in cells between 1st and Last value

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Long
    For i = 2 To Target.Column
        If Cells(Target.Row, i) = "" Then
            If Cells(Target.Row, i - 1) <> "" Then
                Range(Cells(Target.Row, i), Cells(Target.Row, i)).Value = Range(Cells(Target.Row, i - 1), Cells(Target.Row, i - 1)).Value
            End If
        End If
    Next i
End Sub

This sub is activated by clicking on any cell. Same cell marks the end of the loop i.e. to stop the loop just click the cell till which you want to fill the blank cells.

Update: this can be similarly done for other way round as well as asked in this question.

Answer 4

Can be achieved easily without VBA:

Enter something in first row after last blank (same column), select from RMDSADMIN down to that something, =, Up, Ctrl + Enter.