Vertical Scroll Bar Jumps from top to bottom of file instead of bottom of data

[bbader123]

Using Excel 2010 on Windows 7.

This problem is driving me crazy. I have data (formulas) in a worksheet from row 1 to 1000. However, the scroll bar thinks there is data to the bottom of the worksheet, the very bottom!!! So the scroll bar is very small and if you move it even a fraction, you jump from row 1 to row 4830 (or somthing beyond the actual data).

Note the following:
1) I have 4 sheets; Tab 1) A picture of WORD file with instructions; Tab 2) Has worksheet with formulas and macro buttons to sort the data (problem page), Tab 3) Statistics for data on Tab 2; Tab 4) More statistics for Tab 2. I use conditional formatting and Data Validation rules.

What have I tried.

1) Deleting all tabs except tab 2.; followed by a save, even closed and reopened the file.
2) Deleting all rows from 1001 to the bottom of the file; followed by a save, even closed and reopened the file.
3) Clearing all formatting, content, etc. from rows 1001 to the bottom of the file; followed by a save, even closed and reopened the file.
4) Before I deleted the other tabs I changed my formulas to work on rows 3 to 1000 ... this helped speed up my file as well, lesson learned, but didn't help the scroll bar issue.
5) I changed data validation rules to only work on rows 3 to 1000 ... this helped speed up my file as well, lesson learned, but didn't help the scroll bar issue.
6) I changed conditional formatting to work only on rows 3 to 1000 ... this helped speed up my file as well, lesson learned, but didn't help the scroll bar issue.
7) I removed data validation rules and conditional formatting; followed by a save, even closed and reopened the file. No joy.

The only thing that seems to work is to cut/paste content into a fresh sheet and global replace the sheet name in all my formulas.

HELP!!! Surly there is a way to get the scroll bar to fix itself, this is such a basic function of Excel ... good grief!

 

[mole999]

Sub ResetRange()
ActiveSheet.UsedRange
End Sub

Reset the last cell of your Excel worksheet - TechRepublic

 

[Rick Rothstein]

I am not sure mole999's suggestion will work as I thought the "last cell problem" gets cleared when the workbook is closed and reopened (which you said you did). By all means, try it, but I am thinking the problem must be somewhere else (not sure where, though). Just out of curiosity, do you have any VBA event code installed in this particular workbook?

 

[bbader123]

Yes; but the code only works to sort the columns and was modified to only work on the 1st 1000 rows. That was another I changed that I forgot to mention, all formulas and macro's that called an entire column of data now are restricted to only the first 1000. I thought for sure that would fix the issue ... but it didn't. Was reading that the scroll bar thing is a bug in Excel but nothing definitive (in my mind).

 

[mole999]

on the page, where does Control + End take you to

 

[bbader123]

The bottom of the sheet ... row gazillion.

 

[mole999]

then its a format or something holding the last used cell, there are a number of things that claim to repair last cell reset (or one of those freaky damaged sheets)

 

[bbader123]

Yes but what? I have tried to clear ALL, format, content ... I have actually deleted and saved .... what more can I do? I also changed my code, conditional formatting, and macros so nothing is trying to touch anything beyond row 1000. Still, Excel thinks I have some data on the bottom of the sheet??!??!?!?

 

[mole999]

did you run the code above, any result

 

[bbader123]

Yep, no effect.