Autofill the data based on non blank cell in next row?

[Rajesh Kr Joshi]

Hi,

Need a code to autofill the data based on non blank cell in next row (to Right).
For example row 3 has data from A3 till e3 and I have a value in A2 which I wants to autofill to E2.

Thanks
Rajesh

[Admin]

Hi Rajesh,

try this. adjust the range.

Sub kTest()
    
    Dim r   As Range, k, i As Long, c As Long
    
    Set r = Intersect(ActiveSheet.UsedRange, Range("a:e")) '<<=== adjust the range(a:e) part
    
    With r
        .Columns(1).Insert
        .Columns(1).Offset(, -1).FormulaR1C1 = "=counta(rc[1]:rc[" & .Columns.Count & "])"
        k = .Columns(1).Offset(, -1).Resize(, .Columns.Count + 1).Value
        For i = 1 To UBound(k, 1)
            If k(i, 1) = 1 Then
                If i + 1 <= UBound(k, 1) Then
                    For c = 2 To UBound(k, 2)
                        k(i, c) = k(i + 1, c)
                    Next
                    i = i + 1
                End If
            End If
        Next
        .Columns(1).Offset(, -1).Resize(, .Columns.Count + 1) = k
        .Columns(1).Offset(, -1).Delete
    End With
    
End Sub

[Rajesh Kr Joshi]

Hi Admin,

Thanks for your quick reply. This code solved my other issue
However, i am still struggling with my current issue. This code Autofill the same value i have in other row. But i am looking to autofill the value I have in target row.

The attached file will clarify it.

Autofill.xlsx

Thanks in advance

Rajesh

[Rajesh Kr Joshi]

Hi,

Got a solution , not sure how professional it is, but worked for me.

Sub test2()
LastCol = Range("A2").End(xlToRight).Column
lastrow = Range("B2").End(xlUp).Row
Debug.Print lastrow, LastCol
Range("A1").AutoFill Destination:=Range("A1", Cells(1, LastCol)), Type:=xlFillDefault

End Sub

Thanks
Rajesh