Excel：使用 VBA 命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6228087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Named range with VBA
提问by Jort
I'm trying to define a named range in Excel using VBA. Basically, I have a variable column number. Then a loop runs to determine the first empty cell in that particular column. Now I want to define a named range from row 2 of that particular column to the last cell with data in that column (first empty cell - 1).
我正在尝试使用 VBA 在 Excel 中定义命名范围。基本上，我有一个可变的列号。然后循环运行以确定该特定列中的第一个空单元格。现在我想定义一个命名范围，从该特定列的第 2 行到该列中包含数据的最后一个单元格（第一个空单元格 - 1）。
For example, column 5 is specified, which contains 3 values. My range would then be (2,5)(4,5) if I'm correct. I'm just wondering how to specify this range using only integers instead of (E2:E4). Is it at all possible?
例如，指定了第 5 列，其中包含 3 个值。如果我是正确的，那么我的范围将是 (2,5)(4,5)。我只是想知道如何仅使用整数而不是 (E2:E4) 来指定这个范围。有可能吗？
I found this code to define a named range:
我发现这段代码定义了一个命名范围：
'Change the range of cells (A1:B15) to be the range of cells you want to define
Set Rng1 = Sheets("Sheet1").Range("A1:B15")
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1
Could anyone nudge me into the right direction to specify this range using integers only?
任何人都可以将我推向正确的方向以仅使用整数指定此范围吗？
回答by Harag
As your targeting a range of E2:E4 you would need to specify the cell locations. The below function might be of use to you, pass it the column number e.g. 5 and it will retunn the address so 5=E and 27=AA
当您定位 E2:E4 范围时，您需要指定单元格位置。下面的函数可能对您有用，将列号（例如 5）传递给它，它将返回地址，因此 5=E 和 27=AA
ColLetter = ColNo2ColRef(colNo)
Set Rng1 = Sheets("Sheet1").Range(ColLetter & "2:" & ColLetter & "4")
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1
Function ColNo2ColRef(ColNo As Long) As String
ColNo2ColRef = Split(Cells(1, ColNo).Address, "$")(1)
End Function
Hope this helps
希望这可以帮助
EDIT: Or:
编辑：或：
Set rng = Range(Cells(2, 5), Cells(4, 5)) 'E2:E4
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng
回答by osknows
or alternatively
或者
Sub test()
Set rng1 = Cells(2, 2) 'B2
Set rng2 = rng1.Resize(3, 1) 'B2:B4
'or
Set rng2 = Range(rng1, Cells(4, 2)) 'B2:B4
End Sub
or do it directly without looping using
或直接执行而不循环使用
With Sheet1
col = 5 'variable col
Set rng1 = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp))
End With
which is the same as
这与
with sheet1
Set rng1 = .Range(.Range("E2"), .Range("E" & .Rows.Count).End(xlUp))
end with
EDIT: If you're setting up named ranges to change dynamically then you don't need VBA. Enter this directly into the named range in Excel and leave it to auto adjust automatically between E2 and whatever the last item is (assuming no blanks). =$E$2:INDEX($E$2:$E$5000,COUNTA($E$2:$E$5000))(Extend 5000 if you need need more rows)
编辑：如果您将命名范围设置为动态更改，则不需要 VBA。将其直接输入 Excel 中的命名范围，并让它在 E2 和最后一项（假设没有空格）之间自动调整。=$E$2:INDEX($E$2:$E$5000,COUNTA($E$2:$E$5000))（如果需要更多行，请扩展 5000）
