Excel-VBA：在行中创建命名范围，直到单元格内容结束

Imagine an Excel sheet with some rows and some content in each row (i.e. different column-length for each row).

想象一下Excel工作表,每行包含一些行和一些内容(即每行的列长不同)。

In Excel-VBA: How can I create a range-X within column-X that goes from row-cell-2 to the end of the content of this X-column ??

在Excel-VBA中:如何在列-X中创建一个范围-X,它从行单元格2到此X列内容的末尾?

i.e. I would like to create a named range per column and each range shall start from row-2 until the end of each column-length!

即我想为每列创建一个命名范围,每个范围应从第2行开始,直到每列长度结束!

Thanks for any help on this !

感谢您的帮助!

2 个解决方案

#1

Sure you can use this snippet to find the last filled cell in a column and use that row number to set your range.name - just replace the "A" with whatever column you'd like.

当然,您可以使用此片段查找列中最后一个填充的单元格,并使用该行号设置您的range.name - 只需将“A”替换为您想要的任何列。

Sub test()
Dim lastrow As Integer
 lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 Range("A2:A" & lastrow).Name = "RangeColA"
End Sub

If you wanted to do it for each column you could try something like this -

如果你想为每一栏做这件事,你可以尝试这样的事情 -

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

#2

Here is another answer (inspired by Raystafarian's answer) that handles the case where the last used cell in the column appears at or above the second row and also gives more flexibility in the name:

这是另一个答案(灵感来自Raystafarian的答案),它处理列中最后一个使用过的单元格出现在第二行或上面的情况,并且在名称中提供了更大的灵活性:

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

try various things in columns A through E (including leaving a blank column) then test it like this:

在A到E列中尝试各种各样的事情(包括留下一个空白列),然后像这样测试:

Sub test()
    NameColumn "A"
    NameColumn "B"
    NameColumn "C"
    NameColumn "D", "Bob"
    NameColumn "E"
End Sub

#1