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?

Answer 1

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