Hi,

If I understand correctly then your concern is what happens to named ranges
when columns or rows are inserted or deleted. See if the following helps.

Named areas:-

Expand or reduce by the number of columns or rows inserted or deleted in the
middle of the named range.

Move to the right or left by the number of columns inserted or deleted to
the left of the named range.

Move down or up by the number of columns inserted or deleted above the named
range.

In VBA if you want to preserve the named range from the existing first to
last cells irrespective of columns being inserted and/or deleted, then prior
to insertion or deletion of columns and/or rows, then save the address of the
existing first and last cell of the named range and then rename the range
after inserting and/or deleting rows.

The following VBA example demonstrates the above. Copy it into a blank
workbook and try it out:-

Sub Named_Areas()

Dim strFirst As String
Dim strLast As String

'Create a dummy named range to work with.
Sheets("Sheet1").Select
ActiveWorkbook.Names.Add Name:="WorkArea", RefersToR1C1:= _
"=Sheet1!R2C1:R100C7"


'Save the address of first and last cells of _
WorkArea to variables
With Sheets("Sheet1").Range("WorkArea")
strFirst = .Cells(1).Address
strLast = .Cells(.Cells.Count).Address
End With

Range("WorkArea").Select
MsgBox "Named area address before column insertion " _
& Sheets("Sheet1").Range("WorkArea").Address
Range("A1").Select

'Insert columns
Columns("C:E").Insert Shift:=xlToRight

Range("WorkArea").Select
MsgBox "Named area address after column insertion " _
& Sheets("Sheet1").Range("WorkArea").Address
Range("A1").Select


'Rename the work are to the original addresses
ActiveWorkbook.Names.Add Name:="WorkArea", _
RefersToR1C1:=Range(strFirst, strLast)

Range("WorkArea").Select
MsgBox "Named area address after renaming WorkArea " & _
Sheets("Sheet1").Range("WorkArea").Address
Range("A1").Select


End Sub


Regards,

OssieMac


" wrote:

Excel Named Formula Weakly Interacts with a Variable Range on the
Worksheet - Re-Visit

1. Enter an Excel WorkSheet. This time, the named formula is created
in the Define Name space,

TitleCount = SUM(IF(MID(
OFFSET('Sheet1'!WorkArea,0,4,ROWS('Sheet1'!E$2:E6) ,1),1,100)=
MID('Sheet1'!E6,1,100),1,0))

WorkArea is defined a range of A2:G100 on the WorkSheet. TitleCount is
applied in Cell B6.

2. And, the given formula works fine until it's so required to insert
a column between Column 2 and 6 of the WorkSheet ; and similarly, it's
anticipated that there would be more columns added thereof in the
foreseeable future.

3. Then, it behooves revising the formula ; consequently, the affected
part is essentially that of altering the OFFSET function such as
(replacing the figure 4, meant to be Column E, with alternative
expression),

OFFSET('Sheet1'!WorkArea,0,COLUMNS('Sheet1'!A6:E6)-1,ROWS('Sheet1'!E
$2:E6),1),1,100)

4. Testing follows suit by inserting a column at Column C and needless
to say, columns to the right are shifted to follow the prevalent
alphabetical order of the Worksheet.

5. Then, Cell B6 is selected ; Go to Define Name space. Upon closer
examination,

COLUMNS('Sheet1'!A6:E6), remains as is, albeit expecting that to be
updated thus,
COLUMNS('Sheet1'!A6:F6).

6. Needless to say and there too,

ROWS('Sheet1'!E$2:E6) and MID('Sheet1'!E6,1,100),

remain unmoved by the changing state of the WorkSheet.

7. But still, inserting a row at Row 6 of the WorkSheet will instigate
an interaction such that the range in the Define Name space will be
updated like, E6 to become E7.

8. This is Excel 2000. Please share your experience. Regards.