Excel: именованный диапазон с VBA
Я пытаюсь определить именованный диапазон в Excel, используя VBA. По сути, у меня есть номер переменной столбца. Затем выполняется цикл, чтобы определить первую пустую ячейку в этом конкретном столбце. Теперь я хочу определить именованный диапазон от строки 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
Может ли кто-нибудь подтолкнуть меня в правильном направлении, чтобы указать этот диапазон, используя только целые числа?
2 ответа
Поскольку вы нацеливаетесь на диапазон 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
Или в качестве альтернативы
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, если вам нужно больше строк)