hey guys i have this macro ... the 2nd part names ranges based on a list...currently it refers to a4:a19, b4:b19 etc... is thre anyway to adjust it so it uses the =offset function instead???
Sub NameThoseRanges()
Dim nm As Name
Dim oneCell As Range
For Each nm In ActiveWorkbook.Names
If nm.Name <> "WsList" And nm.Name <> "TeamNames" And nm.Name <> "PrintArea" And nm.Name <> "teams" Then
nm.Delete
End If
Next
With Worksheets("Score sheet").Range("Dn1:Dn17")
For Each oneCell In .Cells
On Error Resume Next
If oneCell.Text <> vbNullString Then
.Offset(2, oneCell.Row).Resize(17, 1).Name = oneCell.Text
End If
On Error GoTo 0
Next oneCell
End With
End Sub
cheers
Sub NameThoseRanges()
Dim nm As Name
Dim oneCell As Range
For Each nm In ActiveWorkbook.Names
If nm.Name <> "WsList" And nm.Name <> "TeamNames" And nm.Name <> "PrintArea" And nm.Name <> "teams" Then
nm.Delete
End If
Next
With Worksheets("Score sheet").Range("Dn1:Dn17")
For Each oneCell In .Cells
On Error Resume Next
If oneCell.Text <> vbNullString Then
.Offset(2, oneCell.Row).Resize(17, 1).Name = oneCell.Text
End If
On Error GoTo 0
Next oneCell
End With
End Sub
cheers
