使用列的最后一行的值作为范围？

I have a vba code that determines the date based on 3 cells in excel, it then reformats the date.

我有一个vba代码,它根据excel中的3个单元格确定日期,然后重新格式化日期。

Sub Test()

Dim i As Long
i = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row

Range("K3").Formula = "=DATE(A3,G3,H3)"
Range("K3").AutoFill Destination:=Range("K3:K28")

Set fmt = Range("K3:K28")
fmt.NumberFormat = "ddmmmyyyy"

End Sub

The line that gives a value to "i" (which is 28) determines the last populated cell in that column. I would like to use the value of "i" as K28 in the range.

赋值为“i”(为28)的行确定该列中最后一个填充的单元格。我想在范围内使用“i”的值作为K28。

I am fairly new to VBA and have not been able to figure out how to accomplish this task.

我对VBA还不熟悉,也无法弄清楚如何完成这项任务。

1 个解决方案

#1

You can slightly simplify your code. There is no need to use AutoFill:

您可以稍微简化代码。无需使用自动填充:

Sub Test()
    Dim i As Long
    With Sheet1
        i = .Cells(.Rows.Count, 2).End(xlUp).Row
        With .Range("K3:K" & i)
            .Formula = "=DATE(A3,G3,H3)"
            .NumberFormat = "ddmmmyyyy"
        End With
    End With
End Sub