Sub dynarangeV()
Dim debut As Range
Dim nomrange As String
Set debut = ActiveCell.Cells(1, 1)
debut.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
nomrange = debut.Value
debut.Name = nomrange + "start" 'define the start point of the range
debut.EntireColumn.Name = nomrange + "col" 'name the column of the range
debut.EntireColumn.Interior.ColorIndex = xlNone
ThisWorkbook.Names.Add nomrange & "long", "=MAX(IF(ISNUMBER(MATCH(9^9," & nomrange & "col)),MATCH(9^9," & nomrange & "col)" _
& ",IF(ISNUMBER(MATCH(""zzz""," & nomrange & "col)),MATCH(""zzz""," & nomrange & "col))))"
ThisWorkbook.Names.Add nomrange, "=OFFSET(" & nomrange & "start,1,," & nomrange & "long -ROW(" & nomrange & "start))"
ActiveSheet.Hyperlinks.Add debut, "", nomrange
Range(nomrange & "col").Interior.ColorIndex = 40
debut.Interior.ColorIndex = 46
End Sub