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]

theres a programme file out there called XSFormatCleaner, or I use ASAP utilities. Beyond that can you post your vba

 

[bbader123]

File is on a closed system so I can't go beyond excel to solve this issue. As I noted originally, I removed ALL VBA, macros, buttons, even converted to a non-macro file, removed conditional formatting, removed data validation rules, everything! And excel STILL thinks I have data at the bottom! Just the craziest thing I have ever seen!

 

[mole999]

found this

Application.ActiveSheet.UsedRange

which is supposed to be more effective

 

[bbader123]

Didn't fix the problem ... it's like Excel lost its mind and just thinks the bottom of the file contains data. Crazy!

 

[mole999]

can you create a blank sheet with the values, delete the errant sheet then rename the blank ?

 

[MARK858]

What effect if any does the code below have (please note it will remove any emptystring formulas outside the range). Make sure you have made a copy before running.

Sub LoseThatWeight()

    Dim x As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

        With ActiveSheet
            LastRow = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    MsgBox ActiveSheet.UsedRange.Address
End Sub

 

[mole999]

out of curiosity should/could the RANGE be set to the perceived normal A1:x200 for instance before running

 

[MARK858]

Not sure how exactly you mean to reset it to the perceived range but even if we could I would prefer to get the used range as Excel sees it to match the perceived range which if for some reason the code doesn't reset to then it is easily adaptable to find where the last problem cell is (in theory).

 

[bbader123]

Yes, this is the only solution I have found and this is what I did, rebuilt the sheet from a blank. But was hoping someone would have insight as to why EXCEL is doing this. Probably a bug.

 

[bbader123]

Let me try your code and see if it points to the problem cell. But what I don't get is I deleted all the rows below 1000 and it still thinks data exists at the bottom. This is after changing to a non-macro enabled excel file, removing all conditional formatting, removing all data validation, making all cells locked but not enabling sheet protection, clearing all formatting in the rows below 1000 (clearing ALL actually) ... still, no joy. Crazy dude.