VBA Excel：修改动态命名范围代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17589499/
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
VBA Excel: modify dynamic named range code
提问by karamell
Newbie question: I have module, originally made by Roger Govier.
新手问题：我有模块， 最初由 Roger Govier 制作。
It uses an input cell headerand creates a dynamic named range for the non empty cells positioned under header. The created named range will be labeled as the value of the header cell.
它使用输入单元格header并为位于 下的非空单元格创建动态命名范围header。创建的命名范围将被标记为标题单元格的值。
Private Sub CreateNamedRange(header As range)
Dim wb As Workbook
Dim WS As Worksheet
Dim rStartCell As range
Dim rData As range
Dim rCol As range
Dim lCol As Long
Dim sSheet As String
Dim Rowno As Long
' get table location
Set rStartCell = header
Set WS = rStartCell.Worksheet
Set wb = WS.Parent
sSheet = "'" & WS.Name & "'"
With rStartCell
Rowno = .row
Set rData = .CurrentRegion
End With
Set rData = WS.range(rStartCell, WS.Cells(Rowno, rStartCell.Column))
Set rCol = rData.Columns
lCol = rCol.Column
wb.Names.Add Name:=Replace(rCol.Cells(1).Value, " ", "_"), _
RefersToR1C1:="=" & sSheet & "!" & rCol.Cells(2).Address(ReferenceStyle:=xlR1C1) & ":INDEX(C" & lCol & ",LOOKUP(2,1/(C" & lCol & "<>""""),ROW(C" & lCol & ")))"
End Sub
I want to modify this code so that instead of creating a named range it only returns the returns the range of the what would have been the named range.
我想修改此代码，以便它不创建命名范围，而是只返回返回的命名范围的范围。
Example:
We have a header in A1, and data in A2:A5.
示例：我们在 中有一个标题，在 中有A1数据A2:A5。
Now: If we call CreateNamedRange(.range("A1")), it creates a dynamic named range for A2:A5.
现在：如果我们调用CreateNamedRange(.range("A1"))，它会为 创建一个动态命名范围A2:A5。
Goal: If we call CreateNamedRange(.range("A1"))it returns .range("A2:A5")to a variable in the VBA code:
目标：如果我们在VBA代码中调用 CreateNamedRange(.range("A1"))它返回.range("A2:A5")一个变量：
dim myRange As Range
set myRange = CreateNamedRange(.range("A1"))
回答by varocarbas
First thing you should note is that Subs do not return any value and thus myRange = CreateNamedRange(.range("A1"))does not make any sense (with your Sub; it does make sense with the Function in this answer).
您应该注意的第一件事是 Subs 不返回任何值，因此myRange = CreateNamedRange(.range("A1"))没有任何意义（对于您的 Sub；对于此答案中的 Function 确实有意义）。
The function below returns a range, in the same column that the input range, starting from the next row and including all the ones below until finding a blank cell. This range is called "anyName" (and thus you can access it via Range("anyName")).
下面的函数返回一个范围，在输入范围的同一列中，从下一行开始，包括下面的所有行，直到找到一个空白单元格。此范围称为“anyName”（因此您可以通过 访问它Range("anyName")）。
Private Function CreateNamedRange(header As Range) As Range
Dim curRow As Long: curRow = header.Row + 1
Set tempRange = header.Worksheet.Cells(curRow, header.Column)
Do While (Not IsEmpty(tempRange))
curRow = curRow + 1
Set tempRange = header.Worksheet.Cells(curRow, header.Column)
Loop
Set CreateNamedRange = header.Worksheet.Range(header.Worksheet.Cells(header.Row + 1, header.Column), header.Worksheet.Cells(curRow, header.Column))
CreateNamedRange.Name = "anyName"
End Function
回答by grandocu
If you already have the beginning cell activated you can just use
如果您已经激活了起始单元格，则可以使用
Set myRange = Range(ActiveCell.Address, ActiveCell.Offset.End(xlDown).Address)
to set your range for all entries below the active cell. If you don't have it activated, you can just use your rstartCell reference with an offset
为活动单元格下方的所有条目设置范围。如果你没有激活它，你可以使用带有偏移量的 rstartCell 引用
Set myRange = Range(rStartCell.Offset(1), rStartCell.Offset(1).Offset.End(xlDown).Address)
Then you can just add the named range in the next line
然后你可以在下一行添加命名范围
