Set Range using column and row numbers in VBA - not working

[JAzzyD]

I have VBA code that sets the range as
Set SCHOOLYRRANGE = .Range("L:L")

and this works fine...

I would like to set SCHOOLYRRANGE using column and row numbers
I tried
Set SCHOOLYRRANGE = .Range(Cells(12, 4), Cells(12, 77333))

77333 is the last row
4 is row where data starts, header is in L3

Thanks. Probably something obvious...

 

[MickG]

Try something like this:-
NB:- Notice Dots in code !!!!

Dim SCHOOLYRRANGE As Range
With Sheets("Sheet1")
    Set SCHOOLYRRANGE = .Range(.Cells(4, 12), .Cells(77333, 12))
End With
MsgBox SCHOOLYRRANGE.Address(external:=True)

 

[JAzzyD]

Thank you that works perfectly.
I did solve it by using:
Set SCHOOLYRRANGE = .Range(Cells(4, 12).Address & ":" & Cells(77333, 12).Address)
but your code is much better and concise...

Thanks again

 

[MickG]

You're welcome