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.

466

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