Create a Named Range using Offset

[julhs]

Can I create Named Ranges for "Blank Range One, Two & Three" using the Offset function so that it allows for new blank cells (highlighted yellow)
to be added or deleted between the existing named ranges, "Input_Data" , "Exclusive" , "New_Way" , "Old_Way", that will not get messed up
as they do at the moment by using "Refers to".
The # of blank rows between will NEVER be less than one but could be several.
Another option would be to utilise (in some way) the various FirstRow & LastRow that I have already established below ?
My apologise, yet again for some reason xl2bb has not provided the Named Ranges.

CountBlanksBetweenRanges.xlsm
	A	B	C
1
2
3	"INPUT DATA"	< this is NameRange "Input_Data"
4
5	Has data
6	Has data
7	Has data
8	Has data
9		< will be "BlankRangeOne"
10	"EXCLUSIVE"	< this is NamedRange "EXCLUSIVE"
11
12	Has formula
13	Has formula
14		< will be "BlankRangeTwo"
15
16	"NEW WAY"	< this is NameRange "NEW_WAY"
17
18
19	Has formula
20	Has formula
21		< will be "BlankRangeThree"
22
23
24	"OLD WAY"	< this is NameRange "OLD WAY"
25
26
27	Has formula
28	Has formula
Sheet1

Option Explicit
Sub BlanksBetween()
   Dim FirstRowIP As Long 'of Input_Data
   Dim LastRowIP As Long 'of Input_Data
   Dim FirstRowEX As Long 'of EXCLUSIVE
   Dim LastRowEX As Long 'of EXCLUSIVE
   Dim FirstRowNW As Long 'of NEW_WAY
   Dim LastRowNW As Long 'of NEW_WAY
   Dim FirstRowOW As Long 'of OLD_WAY
   Dim LastRowOW As Long 'of OLD_WAY

  Dim BlankRangeOne As Range
  Dim BlankRangeTwo As Range
  Dim BlankRangeThree As Range

FirstRowIP = Range("Input_Data").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row - 5
LastRowIP = Range("Input_Data").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
FirstRowEX = Range("Exclusive").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row - 3
LastRowEX = Range("Exclusive").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
FirstRowNW = Range("New_Way").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row - 4
LastRowNW = Range("New_Way").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
FirstRowOW = Range("Old_Way").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row - 4
LastRowOW = Range("Old_Way").Cells.Find("*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
'---------------------------------------
' Next line does return RunTime Error 1004, but something along that line to establish "BlankRangeOne"
     BlankRangeOne = Range("A" & LastRowIP + 1 & "A" & FirstRowEX)
     BlankRangeTwo = ??
     BlankRangeThree = ??
End Sub

[/CODE]

 

[myall_blues]

I haven't done anything with your code as I'm not sure what you're trying to do there, but if you define your named ranges with OFFSET up to the first blank then you can insert blank rows at the yellow cells as much as you like without affecting the range.

For example, define range Input_Data as =OFFSET(0,0,COUNTA(A5:A9),1)
Define range Exclusive as =OFFSET(0,0,COUNTA(A12:A14),1)
etc

 

[julhs]

Thanks for response.
I tried your suggestion of

Input_Data as =OFFSET(0,0,COUNTA(A5:A9),1)

however the name manager just changes it to

="OFFSET(0,0,COUNTA(A5:A9),1)"

rendering it not operational, any ideas as to why? (I’m using Excel 2010 if it makes a difference)

I had been defining range Input_Data as =Sheet1!$A$3:$A$8 and Exclusive as =Sheet1!$A$10:$A$13 etc
and that is fine, they auto adjust when adding or deleting blank cells between them.
I’m actually trying to create a named ranges for the blank cells BETWEEN Input_Data and Exclusive, Exclusive and New_Way,
New_Way and Old_Way so I can then use those named ranges for another purposes.

But this is where my problem arose; when I defined range BlankRangeTwo =Sheet1!$A$14:$A$15, and then inserted a cell in $A$14.
BlankRangeTwo auto adjusted to =Sheet1!$A$15:$A$16 and NOT to =Sheet1!$A$14:$A$16, (to include added row)
I realised that if I used BlankRangeTwo =Sheet1!$A$13:$A$15 and then inserted a cell in $A$14, BlankRangeTwo would auto adjusts to =Sheet1!$A$14:$A$16.
It still remains to be seen if I can manipulate the named range “BlankRangeTwo” for those other purposes I mentioned.

 

[felixstraube]

Would this work for you?:

In Column D are the formulas you would have to use in your named ranges.
I think this should work in Excel 2010, but i couldn't test it because i don't have that version anymore.
Let me know.

Book1
	A	B	C	D
1
2
3	"INPUT DATA"	< this is NameRange "Input_Data"
4
5	Has data
6	Has data
7	Has data
8	Has data
9	Blank 1	< will be "BlankRangeOne"		Blank 1
10	"EXCLUSIVE"	< this is NamedRange "EXCLUSIVE"
11
12	Has formula
13	Has formula
14	Blank 2.1	< will be "BlankRangeTwo"		Blank 2.1
15	Blank 2.2			Blank 2.2
16	"NEW WAY"	< this is NameRange "NEW_WAY"
17
18
19	Has formula
20	Has formula
21	Blank 3.1	< will be "BlankRangeThree"		Blank 3.1
22	Blank 3.2			Blank 3.2
23	Blank 3.3			Blank 3.3
24	"OLD WAY"	< this is NameRange "OLD WAY"
25
26
27	Has formula
28	Has formula
Sheet1

Cell Formulas
Range	Formula
D9	D9	=OFFSET(Input_Data, ROWS(Input_Data),0, MIN(ROW(EXCLUSIVE))-MAX(ROW(Input_Data))-1)
D14:D15	D14	=OFFSET(EXCLUSIVE, ROWS(EXCLUSIVE),0, MIN(ROW(NEW_WAY))-MAX(ROW(EXCLUSIVE))-1)
D21:D23	D21	=OFFSET(NEW_WAY, ROWS(NEW_WAY),0, MIN(ROW(OLD_WAY))-MAX(ROW(NEW_WAY))-1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
EXCLUSIVE	=Sheet1!$A$10:$A$13	D9, D14
Input_Data	=Sheet1!$A$3:$A$8	D9
NEW_WAY	=Sheet1!$A$16:$A$20	D14, D21
OLD_WAY	=Sheet1!$A$24:$A$28	D21

 

[julhs]

Thanks for response Felix.
Not in position to test your suggestion as just off out, will have to see if can do it tomorrow.

 

[felixstraube]

Let me know

 

[julhs]

Couldn’t resist the temptation to test it.
Only tested using it for naming range "BlankRangeTwo", and putting into my code

"Range("BlankRangeTwo").Select"

and it IS selecting A14:A15.
Will come back to you

 

[myall_blues]

If you want to try the OFFSET solution then include the sheet name in it. Apologies I left that out.

=OFFSET(Sheet1!A5,0,0,COUNTA(Sheet1!A5:A9),1)