Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP1 - How to correct scrollbar extent?
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"? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP1 - How to correct scrollbar extent?
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 Ron West wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vertical scrollbar in Excel | Excel Discussion (Misc queries) | |||
Charts made in excel 2003 won't print to the correct size in 2007? | Charts and Charting in Excel | |||
Excel scrollbar | Excel Discussion (Misc queries) | |||
insert scrollbar into a cell in Excel | Excel Worksheet Functions | |||
scrollbar reslut makes another scrollbar to show | Excel Discussion (Misc queries) |