Adding to Named Range and Disappearing coloring

[energizek]

Hi,

I have two issues that I've been unable to resolve:

1) I have a named range of cells called "District Staff" which = B56:B77
If a user inserts a row anywhere *except for the last cell of the range* the range automatically expands to include the new cell. However, when a user inserts a row by selecting cell B77 and clicking "insert row" then the range does not expand to include cell B78. Is there anyway to fix this?

I've reprogrammed the insert row command so that the row is automatically inserted above the activecell; however the range still does not expand to include the final row.

2) My application automatically produces pie charts as an output. However, each time I close and reopen the program and enter new data the pie charts become monochrome. If I right click on the chart itself and under "fill" select "vary colors by slice" then the chart looks nice again. Is there anyway to get excel to do this automatically? I have saved and re-saved the program numerous times with these correct settings--but after entering in new data the colors disappear.

Thank you in advance!!
Katie

[mdmackillop]

Can you use a dynamic range name?
=OFFSET(Sheet1!$B$56,0,0,COUNTA(Sheet1!$B$56:$B$100),1)

Re Point 2, Can you post a sample workbook?

[energizek]

THank you for your response.

If I can you dynamic ranges, I'm not sure how. The issue is that I have a column (B) of a budget that is divided into several named range sections ("National_Staff" "District_Staff" "Supplies", etc.) which are stacked on top of one another. The user can insert rows into any of these sections and the named ranges expand appropriately, but if the user inserts a row in the last row of a named range (which is the most likely place where they would insert a row) then the range does not expand to include that row.

e.g. if the user inserts a row after 155, the "National_Staff" range will only include B134:155 (and not B134:B156).

I've attached the code in case this helps.

I will send an example of the chart coloring shortly.

Thanks again,
Katie

[mdmackillop]

Try adding this to the Worksheet module. It will insert 2 rows into a range when the second last cell in column 1 of the range is changed. Ensure that all ranges have at least 2 "spare rows" to start with.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nm, Rng As Range, Rw As Long
If Target.Column <> 2 Then Exit Sub
If Target.Count > 1 Then Exit Sub
For Each nm In ActiveWorkbook.Names
If InStr(1, nm, "[") = 0 Then
If Not Intersect(Target, Range(nm)) Is Nothing Then
Set Rng = Range(nm).Columns(1)
Rw = Rng.Cells.Count - 1
Exit For
End If
End If
Next
If Target.Address = Rng.Cells(Rw, 1).Address Then
Target.Offset(1).Resize(2, 7).Insert
Target.Offset(-1).Resize(2, 7).Copy
Target.Offset(1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Target.Select
End If
End Sub