How to auto populate blank cells using vba

< Back to Search results

 Category: [Excel]  Demo Available 

How  to auto populate blank cells using vba




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:






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.

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:

How to auto copy data from master list to sub worksheets based on data value in one column
How to auto populate comment box when cell value changes
How to use VBA code to auto generate invoice number
How to auto-generate unique ID in Excel VBA UserForm


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.




stars (0 Reviews)