Selecting data (CTRL-SHIFT-END) select more than active data

[Bob Ptacek]

In Excel 2003 I imported data that had 90 columns. Column hdrs were set as
names. The headings started in cell A2. Names were set by selecting all data
by clicking cell A2, CTRL-SHIFT-END (this selects all data, including
headers), then Insert->Names->Create, Select Top Row Only. Each refresh of
data imported more rows of data. This has worked and new rows of data were
include correctly in array formulas.

I have modified the import with SQL to now only import 40 columns of data
from the source file that still has 90 and also eliminated about 1000 older
records. The resetting of names (cell A2, CTRL-SHIFT-END) still lets
formulas work. However the data area that is selected is still pointing to 90
columns and goes down to the last row before 1000 records were eliminated.
While currently that is beyond the current number of records, soon it will be
past that.

I have tried to adjust the label area to include just the current rows and
columns but it continues to reference the old data area (Col & Row). I can
not find any option that I can set and would be grateful if someone can point
me to what my problem might be.

Thanks you, Bob Ptacek

 

[Pete_UK]

I suspect that when you "eliminated" those 1000 records you just
deleted the contents - what you should do is to delete the rows. But,
you can do that now by moving down to the first blank row after your
data and clicking that row identifier. Then hold down the <shift> key
and press <end> once followed by <down-arrow>, then release <shift>.
This will have highlighted all the rows below your data. Then click on
Edit | Delete, and this will adjust your named ranges to cover just
your data. Save the file to make the changes permanent.

Hope this helps.

Pete

 

[Peo Sjoblom]

http://www.contextures.com/xlfaqApp.html#Unused



--


Regards,


Peo Sjoblom

 

[Nayab]

http://www.contextures.com/xlfaqApp.html#Unused

--

Regards,

Peo Sjoblom









- Show quoted text -

Go to Insert->Names->Define
Then select the name you want to modify and then change the cell
reference in the refers to box. That will help you refer to the range
you wish it to refer to