Excel: Named range with VBA

Question

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).

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?

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?

Harag · Accepted Answer · 2011-06-03T14:26:01.307

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

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

score 4 · Answer 2 · 2011-06-03T15:00:30.963

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)

Excel: Named range with VBA

2 Answers2