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, если вам нужно больше строк)

Другие вопросы по тегам