Excel:使用VBA命名范围

Question

我正在尝试使用VBA在Excel中定义一个命名范围。基本上，我有一个可变的列号。然后运行循环以确定该特定列中的第一个空单元格。现在，我想定义一个命名范围，从该特定列的第2行到该列中包含数据的最后一个单元格(第一个空单元格- 1)。

例如，指定了第5列，其中包含3个值。如果我是正确的，那么我的范围将是(2,5)(4,5)。我只是想知道如何只使用整数而不是(E2:E4)来指定这个范围。这有可能吗？

我找到以下代码来定义一个命名范围：

'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

有没有人能把我推向正确的方向，让我只使用整数来指定这个范围？

Answer 1

由于您的目标范围是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

希望这能有所帮助

编辑:或：

Set rng = Range(Cells(2, 5), Cells(4, 5)) 'E2:E4
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng

Answer 2

或者另选地

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

或者直接执行，而不使用

With Sheet1
    col = 5 'variable col
    Set rng1 = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp))
    End With

，它与

with sheet1
    Set rng1 = .Range(.Range("E2"), .Range("E" & .Rows.Count).End(xlUp))
    end with

编辑:如果你设置的命名范围是动态变化的，那么你不需要VBA。将其直接输入到Excel中的命名范围中，并使其在E2和最后一项之间自动调整(假设没有空格)。=$E$2:INDEX($E$2:$E$5000,COUNTA($E$2:$E$5000)) (如果需要更多行，则扩展5000 )