tom,

The formula is now correct, thank you. However, the range name is still
not right. In the following code you can see that my sheet name is
"MSG", in cell A2 I have "test_here", for the actual range name the
macro named it "_MSG, it should have been "test_here_MSG", what do you
think is wrong?

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_MSG", _
RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "MSG!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Regards
Burl