Formula help expanding table?


The subject is probably not a good summary of what I'm looking to do. But, It's the best could come up with.


I would like to know if there is a way to take range A1:A21 and make it appear as range B1:B21


I have column A and need to convert it to look like column B so that I can use those for calculation in other formulas. I was thinking it had to be something with a BYROW / LAMBDA function but I can't make it work.



9 Replies

@Jagodragon What's the logic here?

Screenshot 2022-07-08 at 17.12.17.png

why a 5 next to the 2 and why 4 numbers 2 below it?

that was an error. that '5' should have been a '2'
that's fixed now... sorry

The 4 numbers below it is by the range definition of A1:A21. For each cell in that range, if the cell is blank use last number found, if cell has a number use that number.


Just try with =IF(A2="",B1,A2) and First Row should be header or blank.


@Jagodragon Did something with PowerQuery as I'm not comfortable with VBA. Perhaps it works for you.



Thank you sivakumarrj. I am trying to get this to work with range formulas. so, i need this result but from a single formulas that looks at A1# (A1# = A1:A21). I can not use a copy and pasted formula as I need this to be a dynamic formula. that is to say that A1# could be 1 row, 1000 rows, or more possibly.


Sub fill()

Dim MaxRow As Long
Dim i As Long

MaxRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To MaxRow

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

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

End If

Next i

End Sub

Assuming that there is always a value in cell A1 you can try these lines of code. In the attached file you can click the button in cell E2 to start the macro.

best response confirmed by Jagodragon (Contributor)

Thank you @Quadruple_Pawn 


I didn't actually use your response because I needed to be able to set it for a specific range. But, you confirmed my fear that i would have to create a VBA function for this.


Here is the code I ultimately used.


Function Expand(x As Range) As Variant
'formula to expand values of a column range to fill all cell
'  =Expand(A1:A20) or =Expand(A1#)
    Dim RET As Variant                                      'retun table
    Dim XVal                                                'vriable to hold current num
    Dim i                                                   'counter
    i = 1                                                   'presetting the counter
    XVal = 0                                                'presetting the initial set value
    ReDim RET(1 To x.Rows.Count, 0)                         'defineing the Return array
    For i = 1 To x.Rows.Count                               'For each cell in the intial range dor the following
        If IsNumeric(x(i).Value) = True And x(i) > 0 Then       'check if the value is a number greater than 0
            XVal = x(i).Value                                       'if it is a nuber greater than zero,set the pass thru value to cell value
        End If                                                  '
        RET(i, 0) = XVal                                        'set cell value to updated pass thru value
    Next i                                                  '
    Expand = RET                                            'sends the processed information back to the excel sheet thru formula
End Function