×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Programmatically adding cells to a named range

Programmatically adding cells to a named range

Programmatically adding cells to a named range

(OP)
Can someone tell me how to programmatically add cells to a named range?

For example, I'd like to begin at cell C3 and the next 3 sets of cells space 4 rows apart and the corresponding set in column H ending up with a named range containing C3, H3, C7, H7, C11, H11, C15, H15.

I'll ultimately set up a form that allows me to enter the starting cell, the number rows to count, and the row/column offsets.

Anybody able to point me in the right direction WRT working with the named range?

RE: Programmatically adding cells to a named range

Did I see this question in another place as well?
Anyway, here's my idea on how to solve it:

Option Base 0

Sub Macro1()
Dim StartCell As Range, TotalRows As Integer, RowOff As Integer, ColOff As Integer
Dim i As Integer, Ref As String

'Initialize parameters
Set StartCell = ActiveSheet.Range("C3")
RowOff = 4
ColOff = 5
TotalRows = 3

'Setup the cell reference string
Ref = "="   'the reference string should start with an equal sign
For i = 0 To TotalRows - 1
    Ref = Ref & StartCell.Offset(RowOff * i, 0).Address(External:=True) _
        & "," & StartCell.Offset(RowOff * i, ColOff).Address(External:=True)
    If i < TotalRows - 1 Then Ref = Ref & ","
    'Debug.Print Ref
Next i
'Add (or replace) the name
ActiveWorkbook.Names.Add Name:="Test", RefersTo:=Ref
'Range("Test").Select

End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Programmatically adding cells to a named range

(OP)
Joerd, thank you very much.

I'm not sure if you saw this question somewhere else. This was my only post and I did a search but didn't find the answer.

No matter, though. Your code is precisely what I was trying to accomplish.

Thanks again for sharing your skill with me.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources