Working with "vertically stacked" dynamic named ranges and dealing with shifting rows

[mroach]

Greetings,

I am very new to VBA and have been preparing a worksheet using code obtained by cherry-picking various bits of code posted by you kind folks here. I would be very grateful for some assistance with what I hope will be my last step. I think the fix will be relatively simple for you experts but the challenge will be for me to explain the problem adequately.

I have a set of dynamic named ranges stacked within the same series of columns and separated by empty rows. For example:

dynamic range 1 is A5:G7

dynamic range 2 is A11:G20

dynamic range 3 is A24:G26

et cetera


I have toggle buttons for displaying and hiding each of the dynamic ranges (toggle button 1 controls displaying and hiding dynamic range 1 and toggle button 2 controls displaying and hiding dynamic range 2).

This is the code for the toggle button 1:

Private Sub ToggleButton1_Click()
ActiveWorkbook.Names.Add Name:="ProjectsDivA", RefersToR1C1:= _
"=Sheet1!r5c1:INDEX(Sheet1!r5c1:r100c1,MATCH(TRUE,Sheet1!r5c1:r100c1="""",0))"
ActiveWorkbook.Names("ProjectsDivA").Comment = ""

Range("ProjectsDivA").Select
If ToggleButton1.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Selection.EntireRow.Hidden = False

Else
'This area contains the things you want to happen
'when the toggle button is depressed
\
Selection.EntireRow.Hidden = True

End If
End Sub

I also have an “add row” button controlling a macro for adding a row to each dynamic range so that additional data can be entered. This macro is operating as intended and the newly added rows get recognized by the dynamic ranges as desired.

The issue is that the output from the second toggle switch controlling dynamic range 2 becomes buggered if a new row is added to the dynamic range 1. Dynamic range 2 was originally was defined as beginning at A11 but obviously, after adding a row to the dynamic range 1, it ends up at A12.

I think I could address this issue by adding code to search for a text label appearing in the first cell of the dynamic range and to identify the cell in which it resides. Then, this cell address must be plugged into the code below where CELLADDRESS is indicated.

ActiveWorkbook.Names.Add Name:="ProjectsDivB", RefersToR1C1:= _
"=Sheet1!CELLADDRESS:INDEX(Sheet1!CELLADDRESS:r100c1,MATCH(TRUE,Sheet1!CELLADDRESS:r100c1="""",0))"

This would allow the boundary of the second dynamic range to be adjusted accordingly if rows are added to the first dynamic range.

So, to summarize my request, I need the code to identify the cell containing the text label that identifies where a given dynamic range begins and to add the cell identification information into the code to allow it to be recognized even if it gets shifted downwards due to addition of rows above it.

I hope that this makes sense and I thank you for providing this service and for taking the time to read this. I would be pleased to provide any clarification that may be necessary.

Best regards,

Mark

 

[p45cal]

Perhaps, instead of looking for a text label in a cell to identify the top cell of a dynamic range, why not just give that cell a non-dynamic name (eg. ProjectsDivATopCell), then base your dynamic name off that static name?
Adding rows above that static name will move that static name down.
You can define that static name manually once and for all, rather than in the code.

 

[mroach]

p45cal - This is an awesome and elegantly simple solution. It worked perfectly. Thank you very much for helping me with my first efforts to get the hang of this stuff.

All the best!

Mark