Changing Size of Named Range

[chrish47]

Hi,

This is probably a simple question, but I can't seem to get it to work. How can I resize a named range to be one row less that it was previously defined as?

I have a name "Trav" in a sheet that and this name is defined as ='Trav Data'!$B$4:$Q$6, a macro inserts rows from 5 to xxx, which changes the name to be ='Trav Data'!$B$4:$Q$XXX+1 (where xxx+1 is the last row, which is blank). Macros then refresh a pivot table from this data, but the pivot table has one blank row (the xxx+1 row), so I would like to resize the named range once the first macro runs.

I have tried: ActiveSheet.Range("Trav") = Range("Trav").Offset(-1, 0), but this replaces the range, not the name.

Thanks,
Chris

 

[SkipVought]

Hi,

I'd suggest using the OFFSET function in Insert/Name/Define - Refers To:
[tt]
=OFFSET('Trav Data'!$B$4,0,0,counta('Trav Data'!$B$B),counta('Trav Data'!$4:$4))
[/tt]
assuming that NOTHING is in
B1:B3
Below the table in column B
A4
Right of the table in row 4

This will define a DYNAMIC range.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Have you tried this ?
With Range("Trav")
Names("Trav").RefersTo = "='Trav Data'!" & .Range(.Cells(1, 1) _
, .Cells(.Rows.Count - 1, .Columns.Count)).Address
End With

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[chrish47]

Thanks for the quick responses. I tried Skips suggestion, but the name wasn't visible, and I wasn't exactly sure what it was doing, so I tried PH's, and it would work except for some strange results.

Instead of shortening the range by one row, it shrinks it by one, shifts it left one column and down 3 rows (my range starts at cell B4 if that makes a difference). I tried to check the code, and I am not sure why this is shifting.

Here is what I did:
With Range("Trav")
.Cells(1, 1).Select
.Cells(.Rows.Count - 1, .Columns.Count).Select

Names("Trav").RefersTo = "='Trav Data'!" & .Range(.Cells(1, 1) _
, .Cells(.Rows.Count - 1, .Columns.Count)).Address

End With

When I step thru this
.cells(1,1).select = C4
.cells(.Rows.Count... = Q30

This is what I want the range to be set to C4:Q30, however when the next line of code runs, it sets the name RefersTo:= ='Trav Data'!$C$7:$R$33, when it should be $C$4:$Q$33. I don't know why this is shifting (I am guessing because I start at B4 rather than A1, but I don't know how to fix it).

Any ideas? Thanks for the great info, this looks like it will work, if I can figure out how to deal with the shifting.

Thanks,
Chris

 

[PHV]

Sorry for the typo:
Names("Trav").RefersTo = "='Trav Data'!" & Range(.Cells(1, 1) _
, .Cells(.Rows.Count - 1, .Columns.Count)).Address


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Your Name that you define in Insert/Name/Define can be used in the Pivot Table source data assignment.

Where did you not see what you were looking for?

In the Pivottable Wizard Step 2 of 4, hitting F3 brings up the list of named ranges in the workbook, INCLUDING the named range defined using the OFFSET Function.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loomah]

Hi
Just a variation on a theme

With Range("Trav")
    Names("Trav").RefersTo = "='Trav Data'!" & _
        .Resize(.Rows.Count - 1, .Columns.Count).Address
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[chrish47]

Skip,

The range that was selected by the offset command still had a blank row on the bottom (and I didn't understand the offset command enough to figure out why). This is used in an nVision report (nVision uses Row 1 and Column A to store values used by the report generator - which may have been causing the problems with the offset).

The formula from PHV worked, but can someone explain why adding the leading . to Range caused the shift?

Thanks,
Chris

 

[PHV]

The dot caused the Range to be relative of the 1st cell of your named range.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Chris,
[tt]
=OFFSET(UpperLeftCellReference,0,0,COUNTA(LeftColumnReference),COUNTA(FirstRowReference))
[/tt]
If the upper left cell of the table table on SheetA is C3, then
[tt]
UpperLeftCellReference: SheetA!$C$3
LeftColumnReference: SheetA!$C:$C
FirstRowReference: SheetA!$3:$3
[/tt]
AND

there must be NOTHING ABOVE the FIRST ROW of data in the table in COLUMN C
there must be NOTHING BELOW the LAST ROW of data in the table in COLUMN C
there must be NOTHING to the LEFT of the FIRST COLUMN of data in the table in ROW 3
there must be NOTHING to the RIGHT of the LAST COLUMN of data in the table in ROW 3

If your table is set up this way, the OFFSET function will DYNAMICALLY change as rows of data are added or deleted.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884