Programmatically adding cells to a named range 1

[Binary]

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?

 

[joerd]

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 & &quot;,&quot;
    'Debug.Print Ref
Next i
'Add (or replace) the name
ActiveWorkbook.Names.Add Name:=&quot;Test&quot;, RefersTo:=Ref
'Range(&quot;Test&quot;).Select

End Sub

Cheers,
Joerd

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

 

[Binary]

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.