adjust code to make range dynamic

[d0wnt0wn]

is there any way that this can be adjusted so the named range that is created is dynamic?

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

 

[stanleydgromjr]

d0wnt0wn,

The following will look for the last row of data in column Dn, from the last row of the worksheet, up.

Try:

  With Worksheets("Score sheet").Range("Dn1", Range("Dn" & Rows.Count).End(xlUp))
    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

Have a great day,
Stan

 

[d0wnt0wn]

hi stanley... i should have been a bit more clear...... and i left out a bit of code that may have confused.

dim onecell as range
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

dn1:dn17 is a range of a preset list..... this i do not need to make dynamic..... but what the code does is it takes the names in dn1 to dn17 and creates named ranges with the same names in the list and they refer to dq2:dq17,dr2:dr17 etc (depending how many new ranges there are)..... its the dq and dr and etc ranges that I want to be dynamic