R
Ron West
This Excel VBA code gives the correct number of Rows and Columns being used
in my Excel 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 the Access 2007 import wizard (and/or Excel's internal
scroll range) to match "Cells.Rows.End(xlDown).Row" and
"Cells.Columns.End(xlToRight).Column"?
in my Excel 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 the Access 2007 import wizard (and/or Excel's internal
scroll range) to match "Cells.Rows.End(xlDown).Row" and
"Cells.Columns.End(xlToRight).Column"?