Hi dyana
You can try this code
Sub nameRange()
Dim myCol As Integer, myRng As Range
Dim myRow As Integer, myRng2 As Range
Dim initialRow As Integer, initialCol As Integer
Dim referRng As String
Set myRng = Sheets("sheet1"

.Range("G1:AF1"

myCol = Application.CountA(myRng) + 6 'the offset is 6 from the first column
Set myRng2 = Sheets("sheet1"

.Range("G:G"

myRow = Application.CountA(myRng2)
Sheets("sheet1"

.Range("G1"

.Select
initialRow = ActiveCell.Row
initialCol = ActiveCell.Column
ActiveSheet.Range(Cells(initialRow, initialCol), Cells(myRow, myCol)).Select
referRng = "=Sheet1!R" & initialRow & "C" & initialCol & ":R" & myRow & "C" & myCol
ActiveWorkbook.Names.Add Name:="dataRange", RefersToR1C1:=referRng
ActiveSheet.Cells(myRow, myCol).Select
End Sub
Hope this helps.
rgrds
LSTAN