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?
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
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
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.