"Last cell" in Excel 2003 is way past end of data, how do i fix?

[silverfox52]

When I use "Go To", "Special", "Last cell" in my spread sheet, the cursor
goes many rows past the end of the data. ie. about 36 rows past the last data
row.

This is a problem because when I import this data into a blank Access table
all 43 rows are imported (36 are blank) and the import gives me an error
message.

I've tried deleting from the last data row +1 past the row of the "last
cell" but the last cell remains in the same relative location.

 

[T. Valko]

You need to reset the used range:

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

 

[FSt1]

hi
i think excel has lost it used range reference. see this site to reset.
http://www.contextures.com/xlfaqApp.html#Unused

regards
FSt1

 

[Jim Thomlinson]

Delete (not clear contents) the blank rows. Save the workbook and the last
cell should be reset.

 

[Elkar]

The Last Cell will grow as data is added to a spreadsheet. But will only
shrink, when the workbook is saved. So, even if you delete all the cells
below and to the right of your data, the last cell will not be updated until
you save your workbook.

HTH
Elkar

 

[Shane Devenshire]

Hi,

And for future reference Ctrl+End does the same thing as Edit, Go To,
Special, Last Cell.

 

[Sheeloo]

The most common cause of the last cell being set outside the worksheet range
that is currently in use is excessive formatting. When you format whole rows
and columns, some types of formatting can cause the last cell to be set to a
cell far below or to the right of the actual range that is in use.

Try to select the extra rows and choose Edit->Clear All
Repeat with Columns, if required.

Test by GoTo->Special and choose Current Region...

 

[silverfox52]

The save was what was needed, thanks

The Last Cell will grow as data is added to a spreadsheet. But will only
shrink, when the workbook is saved. So, even if you delete all the cells
below and to the right of your data, the last cell will not be updated until
you save your workbook.

HTH
Elkar

 

[Gord Dibben]

Sheeloo

Couple or three points....................

Has nothing to do with excess cells being formatted.

Once upon a time the excess range was filled then "cleared", leaving behind
the false used range.

Edit>Clear All will not reset the used range.

You must Edit>Delete

And selecting Current Region will not show you the false used range.

Current Region only shows contiguous non-blank cells in any region.

CTRL + End will show you the used range.

Workbook must be saved before the new usedrange is set.


Gord Dibben MS Excel MVP

 

[dk]

When I pressed Ctrl+End, the cursor went WAAAAY to the end of the
spreadsheet, rather than just to end of of the area where the data is
located. How do I accomplish that?

 

[T. Valko]

Sounds like you need to reset the used range. See this:

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

 

[Roger Govier]

Hi

Select a column to the right of your data>Control+Shift+Cursor right arrow
to select all columns to the right>Right click>Delete
Select a row below your data>Control+Shift+Cursor down arrow to select all
rows below>Right click>Delete

Save the file. Close the file. Reopen