How to autofill data using vba macro-CodePudding

Hi i currently have data in cell F2,G3,H4,I5 etc (diagonal line, the upper part on this diagonal line is blank cell). i want to autofill the same data downwards to row 50 for eg. Instead of me copy & paste manually is there a quicker way to have it done via code? Because the data can be lengthy ~

CodePudding user response：

Please, try this way:

Sub fillDownDiagonalRange()
   Dim sh As Worksheet, rngD As Range, A As Range, x as long
   
   Set sh = ActiveSheet
   Set rngD = sh.Range("F2,G3,H4,I5")
   x = 50 ' how many rows to be filled  down
   
   For Each A In rngD.Areas
        A.AutoFill Destination:=Range(A, A.Offset(x))
   Next A
End Sub

CodePudding user response：

Try this:

Sub Copy()
    Dim Rng As Range
    
    Set Rng = Range("F2")
    
    Do
        Rng.Copy Rng.Offset(1, 1)
        Set Rng = Rng.Offset(1, 1)
    Loop until Rng.Row >= 50
    
End Sub

or this in case you wanted to copy the data down and not diagonally:

Sub CopyDownDiagonalData()
    Dim Rng As Range
    
    Set Rng = Range("F2")
    
    Do
        Rng.Copy Rng.Resize(50 - Rng.Row   1)
        Set Rng = Rng.Offset(1, 1)
    Loop Until Rng.Value = ""
    
End Sub

CodePudding user response：

Sub t()
Dim i, startRow, lastRow
    lastRow = 50
    For i = Range("F2").Column To (ActiveSheet.UsedRange.Column   ActiveSheet.UsedRange.Columns.Count) - 1
        startRow = ActiveSheet.Columns(i).End(xlDown).Row
        Range(Cells(startRow   1, i), Cells(lastRow, i)) = Cells(startRow, i).Value2
    Next
End Sub