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.
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
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.
|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|