OzGrid

How  to auto populate blank cells using vba

Requirement:

The user is using the code provided below and need some revisions and not sure how to tweek it.
The code the user is using is:

Sub With_Loop()
Dim c As Range, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountA(Range("A:A")) = lr Then MsgBox "No empty cells in column A!": Exit Sub
For Each c In Range("A2:A" & lr).SpecialCells(4)
c.Value = c.Offset(, 1).Value
Next c
End Sub

What the user needs is same functionality, but for the blank lines to copy the populated cell in column A and auto populate it down in all the blank cells till the next populated cell.
example attached and below:

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1206933-auto-populate-blank-cells-using-vba

Solution:

I assume the data is imported from another source/database.
The Cells that are not recognized as empty probably contain a zero-length string.
If this happens more, you could run something like this.

Sub AAAAD()
Dim c As Range, lr As Long, Area As Range
lr = Cells(Rows.Count, 2).End(xlUp).Row

    For Each c In Range("A2:A" & lr)
        If Len(c) = 0 Then c.Clear
    Next c

    For Each Area In Range("A2:A" & lr).SpecialCells(4).Areas
        Area.Value = Area.Cells(1).Offset(-1).Value
    Next Area
    
End Sub

The last three lines, excl the End Sub line, should work to fill the empty cells if you don't have the zero-length string in one of the cells.

You might have to change this

Area.Value = Area.Cells(1).Offset(-1).Value

to this

Area.Value = Area.Cells(1).Offset(-1).MergeArea.Cells(1).Value

Please refer to https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1206933-auto-populate-blank-cells-using-vba for additional ideas regarding this code.

Obtained from the OzGrid Help Forum.

Solution provided by jolivanes.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery