I need to name the cells of every 9th row of column-F to be \"SubT1\" so i can s

Question

I need to name the cells of every 9th row of column-F to be "SubT1" so i can sum them easily. I will need to grow the range as needed, so i may start w/800 rows but could grow to any number. When I copy and paste a block of 9 rows at a time, it will not copy the name of the cell and i have to name each cell of each row. (This is done for many columns with different names and sums).

So when i copy a new block of 9 rows at the bottom from the bottom, i need the name of the cells to copy also...

( i can pre-name the cells all the way to say 4000 as the pattern will never change FOR THE BLOCK OF 9 ROWS, but I'm trying to avoid clicking on every 9th cell up-to 4000 and then naming them) KEEP IN MIND THAT THE ROWS WILL GROW AFTER THEY HAVE BEEN COPIED DOWN...Thanks a fortune for the help.

Explanation / Answer

No, it won't work if you add rows in the middle: from your description, I thought it was blocks of 9, which could grow by blocks of 9.

But you can predefine your range to include many more cells than you are currently using. For example, this macro will create a named range that stands up to insertions:

(Note that Sub1 is not a valid range name for Excel 2007+ since that is a valid cell address.)

Sub TestMacro()
Dim rngC As Range
Dim i As Integer

Set rngC = Range("H8")

For i = 1 To 1000
Set rngC = Union(rngC, Cells(i * 9 + 8, 8))
Next i

ThisWorkbook.Names.Add "SubRange2", rngC

With Range("SubRange2")
.Interior.ColorIndex = 3
.Value = 4
End With

Range("H2").Formula = "=SUM(SubRange2)"
End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects