如何使用vba宏自动填充数据

Question

我目前在单元格F2,G3,H4,I5等中有数据(对角线，此对角线的上部为空白单元格)。

我想自动填充相同的数据向下到第50行例如。而不是我copy &手动粘贴是否有更快的方法通过代码完成?

因为数据可能很长。

Answer 1

请尝试这样做:

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

Answer 2

试试这个:

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

或者如果你想向下复制数据，而不是对角线:

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

Answer 3

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