Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lastrow).Name = "RangeColA"
End Sub
如果你想为每一列做这个,你可以尝试这样的事情 -
Sub test()
Dim lastrow As Integer
Dim letter As String
Dim lastcol As Integer
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 65 To 65 + lastcol
letter = Chr(i)
lastrow = Cells(Rows.Count, letter).End(xlUp).Row
Range(Cells(2, letter), Cells(lastrow, letter)).Name = "RangeCol" & letter
Next
End Sub
Sub NameColumn(Col As String, Optional ColName As Variant)
Dim n As Long
Dim myName As String
If IsMissing(ColName) Then ColName = "Range_" & Col
n = Cells(Rows.Count, Col).End(xlUp).Row
If n <= 2 Then
Range(Col & "2").Name = ColName
Else
Range(Col & "2:" & Col & n).Name = ColName
End If
End Sub
在A到E列中尝试各种事情(包括留下一个空白列),然后像这样测试:
Sub test()
NameColumn "A"
NameColumn "B"
NameColumn "C"
NameColumn "D", "Bob"
NameColumn "E"
End Sub
2 回答
当然,您可以使用此片段查找列中最后一个填充的单元格,并使用该行号设置
range.name
- 只需将"A"替换为您想要的任何列 .如果你想为每一列做这个,你可以尝试这样的事情 -
这是另一个答案(灵感来自Raystafarian的答案),它处理了列中最后一个使用过的单元格出现在第二行或上面的情况,并且在名称中提供了更多的灵活性:
在A到E列中尝试各种事情(包括留下一个空白列),然后像这样测试: