How to conditionally copy value of one cell to the other cells in the same row using formula or macro

[FastThomas]

Hi All,

I have a data table like this:
[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]T[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]G[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

After using formula or macro, it should look like this:
[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]T[/TD]
[TD]T[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]G[/TD]
[TD]1[/TD]
[TD]G[/TD]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]
All 0's has been replaced by the text in the cell from Column A in that row.

Have no idea how to do this. The real data has about 1/4 million rows and 1,000 columns.

Thanks a lot for your help.

FastThomas

 

[JackDanIce]

Welcome to the board. Try:

Sub ReplaceZeroWithColA()

    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 1).End(xlUp).row
    y = Cells(1, Columns.Count).End(xlToLeft).Column
    
    arr = Cells(1, 1).Resize(x, y).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            If arr(x, y) = 0 Then arr(x, y) = arr(x, 1)
        Next y
    Next x
    
    Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    
    Erase arr

End Sub

 

[FastThomas]

Thanks a lot for your quick response. The code worked beautifully.

Thanks again for your help!

Thomas.

 

[JackDanIce]

You're welcome