Excel 2007 SP1 - How to correct scrollbar extent?

[Ron West]

This code gives the correct number of Rows and Columns being used in my
worksheet:-

Dim x As Worksheet
Set x = Worksheets("Store")
Debug.Print x.Cells.Rows.End(xlDown).Row
Debug.Print x.Cells.Columns.End(xlToRight).Column

However, due to accidental over-scrolling, the displayed Excel scrollbars
represent a range of about 40 columns (instead of my sheet's correct number
of 27) and 65535 rows (instead of my sheet's correct number of 1253).

This is a major problem with Office 2007 (I don't remember it happening with
earlier versions) because when you try to import the worksheet into an Access
2007 table, you get 65535 (mostly blank) rows imported instead of 1253 and
you have to mess around identifying the blank rows and deleting them.

You also get error messages that Field28 doesn't exist, etc, if you try to
import to an existing table instead of a new one.

How do you correct Excel's internal scroll range (and/or the Access 2007
import wizard) to match "Cells.Rows.End(xlDown).Row" and
"Cells.Columns.End(xlToRight).Column"?

 

[Dave Peterson]

You can reset the last used cell on the worksheet. This last used cell is where
you go when you hit ctrl-end.

You can find some techniques at Debra Dalgleish's site for resetting the last
used cell:
http://www.contextures.com/xlfaqApp.html#Unused