excel - 通过特定范围运行 VBA

Question

我得到了这个代码，但它只适用于一行。

如何让它在整个列上运行？

Public Sub StripLastName()

Dim strLastName As String

strLastName = Range("A2")
strLastName = Right(strLastName, 8)
strLastName = Trim(strLastName)

Range("C2") = strLastName

End Sub

标签： excelvba

Answer 1

另一个没有循环的解决方案：

Public Sub StripLastName()

    Dim strLastName As String
    Dim LastRow As Long

    'Create a with statement including the worksheet you want work with
    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        .Range("C2:C" & LastRow).Formula = "=TRIM(RIGHT(OFFSET(C2,0,-2),8))"

    End With

End Sub