repeating values until...

[bertusavius]

I'm facing a small problem I can't figure out.


I've got a table and in a particular column there's a number of values. Not all the cells in this column are filled however, so there's an irregular en variable sequence of filled and empty cells in this column.

I would like to let excel repeat the value of the filled cells down the empty cells below the filled cells, without having to use macros.

So for example I'd like to let excel repeat the value in cell a1 in the empty cells below a1 untill there is anonther filled cell in the column. This value will be repeated also until the next filled cell, etc.


I sure hope it's not too cryptic.
Thanx in advance.

 

[Jon von der Heyden]

One way:

Highlight the range containing the data.

On the menu bar go Edit > GoTo > Special > Blanks

Enter = and using the up arrow select the cell above, e.g:

=A1

Commit with CONTROL+ENTER

 

[xld]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"    '<=== change to suit
Dim i As Long
Dim LastRow As Long

    With ActiveSheet
        
        LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
        For i = 2 To LastRow
        
            If .Cells(i, "A").Value = "" Then
            
                .Cells(i, "A").Value = .Cells(i - 1, "A").Value
            End If
        Next i
    End With
    
End Sub