Excel short scroll bar a million empty rows

LaPlume · #1 08-16-2023, 03:51 PM

Using Excel 2021 Pro, but I think this problem is common to many versions.

I have a workbook with four sheets, created years ago. Each sheet has different formatting. Two of the sheets are plagued by the short vertical scrollbar problem, whereas the other two sheets have appropriately-sized scrollbars. I've tried selecting all rows below the bottom active row and using clear>all, but it didn't work. I also tried entering ActiveSheet.UsedRange in the VB Immediate window, but that didn't do anything. On one of the problem sheets, if I select all the rows, the last row is 1,048,576, and the scroll bar (probably not the right term) remains small. On the non-problem sheets, I can scroll quickly from top to bottom (the last row with data). If I drag that scroll bar to the bottom and then continue dragging, the bar instantly changes from small to long/tall. Hope that make sense.

What am I not seeing about the formatting or layout of the sheets with the good scroll bars versus the formatting or layout of the sheets with the short scrollbars?

There are no comments on the problem sheets nor any cell contents below the apparent bottom rows. The sheets have no macros.

Can someone please help me fix this common problem?

LaPlume · #2 08-29-2023, 12:52 PM

I originally posted this two weeks ago. I got no replies. I suppose this is a boring topic for the experts, but that doesn't make it any less important to me. I'd be grateful if someone would refer me to a solution from older posts or just post the solution with steps a toddler could follow. I read the posts related to this issue, but they didn't help me resolve my problem. Thank you.

Quote:

Originally Posted by LaPlume

Using Excel 2021 Pro, but I think this problem is common to many versions.

I have a workbook with four sheets, created years ago. Each sheet has different formatting. Two of the sheets are plagued by the short vertical scrollbar problem, whereas the other two sheets have appropriately-sized scrollbars. I've tried selecting all rows below the bottom active row and using clear>all, but it didn't work. I also tried entering ActiveSheet.UsedRange in the VB Immediate window, but that didn't do anything. On one of the problem sheets, if I select all the rows, the last row is 1,048,576, and the scroll bar (probably not the right term) remains small. On the non-problem sheets, I can scroll quickly from top to bottom (the last row with data). If I drag that scroll bar to the bottom and then continue dragging, the bar instantly changes from small to long/tall. Hope that make sense.

What am I not seeing about the formatting or layout of the sheets with the good scroll bars versus the formatting or layout of the sheets with the short scrollbars?

There are no comments on the problem sheets nor any cell contents below the apparent bottom rows. The sheets have no macros.

Can someone please help me fix this common problem?

ArviLaanemets · #3 08-30-2023, 12:09 AM

What the heck is "short vertical scrollbar"? Do you mean the rectangular part on right scrollbar you click on to scroll up/down? This one changes dimension accordingly the number or rows in used area on sheet.

When this is what you are asking about, then getting it short means, that you really have something entered into some cell way down. Use Ctrl + End to locate last used cell on sheet.
In case it will be on the max allowed column or max allowed row for your Excel version and there is not any real entry, then it means for some column you have defined something what makes Excel to mark all rows used for this column (I have encountered such cases in past, but I din't remember anymore what kind of thing this was. And having it defined for entire row/column means, you can get rid of it only deleting rows/columns fully - otherwise the problem is recreated). When this is the case, then the simplest way to correct it is to create a new sheet, copy all data from range you count the proper one only (values, formulas, formats, etc.) from compromised sheet to new one, and then delete the compromised sheet.
When otherwise, then either clear any info from last cell (e.g. dragging some unused cell over it), or delete all rows (when the last cell was in some column used for your real data) or columns (when the last used cell was in some column to right of any columns used for your real data) from last row/column below/right of your real data until row/column of last used cell your did locate, or until last row/cell of sheet.

LaPlume · #4 08-30-2023, 09:13 AM

Quote:

Originally Posted by ArviLaanemets

What the heck is "short vertical scrollbar"? Do you mean the rectangular part on right scrollbar you click on to scroll up/down? This one changes dimension accordingly the number or rows in used area on sheet.

Yes, the rectangular part on the right scrollbar. It's shaped like an elongated oval. On some tabs of this seven-tab workbook, dragging that little oval is perfectly responsive. If I use my mouse to drag the scroll button down (on this tab, the sheet has only 39 rows of data), it moves fast from the top to the bottom. There are still numerous uninhabited rows below row 39, but they have no effect on the scrolling.

Quote:

Originally Posted by ArviLaanemets

When this is what you are asking about, then getting it short means, that you really have something entered into some cell way down. Use Ctrl + End to locate last used cell on sheet.
In case it will be on the max allowed column or max allowed row for your Excel version and there is not any real entry, then it means for some column you have defined something what makes Excel to mark all rows used for this column (I have encountered such cases in past, but I din't remember anymore what kind of thing this was. And having it defined for entire row/column means, you can get rid of it only deleting rows/columns fully - otherwise the problem is recreated). When this is the case, then the simplest way to correct it is to create a new sheet, copy all data from range you count the proper one only (values, formulas, formats, etc.) from compromised sheet to new one, and then delete the compromised sheet.
When otherwise, then either clear any info from last cell (e.g. dragging some unused cell over it), or delete all rows (when the last cell was in some column used for your real data) or columns (when the last used cell was in some column to right of any columns used for your real data) from last row/column below/right of your real data until row/column of last used cell your did locate, or until last row/cell of sheet.

For one of the sheets that's exhibiting the problem, I selected all the rows with data, that is, rows 1 through 422 (I did NOT select the whole sheet), copied, and pasted to a new tab/sheet using Paste Special>All, then also pasted column widths. Nothing changed. The pasted content still has a very short scroll bar (or maybe it's a very long scroll bar; semantics), by which I mean dragging the scroll "button" down is nearly impossible, because it scrolls all 422 rows when it's moved about 1/16" (assuming the workbook is full or nearly full screen on a 27" monitor).

If I have some data entered in a cell "way down," it should not have been copied when I selected only 422 rows.

I've tried the following a few times now:
Ctrl+End shows me the last row with data, which is 422 as expected. Selecting row 423 and using Ctrl+Shift+down arrow selects rows 423 to 1048576. Then on the Home tab>Cells section, using Delete Sheet Rows OR Delete Cells deletes the rows (well, it deletes any data in the rows), but it doesn't fix the problem with the short scroll bar.

Thanks.

AllekieF · #5 10-10-2023, 08:08 AM

Usually this is an issue with a generally applied formatting, i.e., applied to an entire row or column. This applies formatting info to all cells in the row/column, causing Excel to think that all these cells are used (and increasing the workbook size excessively).

What happens if you copy-paste to a new sheet without formatting? Pasting column widths using the right-mouse quick menu also pasts cell formatting. Try using the actual paste-special menu, which has more options.

LaPlume · #6 10-10-2023, 09:16 AM

I'll investigate. Not sure how I did the formatting; it's a multi-tab workbook, and other tabs don't exhibit this problem. Thanks for your input. I'll follow up.

NoSparks · #7 10-11-2023, 04:41 AM

If you're open to a VBA suggestion, this code that MARK858 over at MrE posted a few years ago has worked for me in the past.
Don't know if it will for you but might be worth giving it a try.

Code:

Sub LoseThatWeightx()

    Dim xx As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    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
    
    xx = Application.ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = True
End Sub

LaPlume · #8 10-11-2023, 07:03 AM

<If you're open to a VBA suggestion, this code that MARK858 over posted a few years ago has worked for me in the past.>

Thanks! I'll brush up on VBA and give the code a try.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel rows hidden when it is empty	allex011	Excel Programming	5	03-16-2020 09:55 PM
Delete all empty rows in all tables	fbucaram	Word VBA	6	01-05-2018 03:04 PM
Delete Empty Table Rows	cltay87	Word VBA	4	02-27-2017 04:23 AM
Macro to delete all empty rows from all tables	braddgood	Word VBA	15	10-02-2015 01:54 PM
Delete All empty Rows - Print - Undo all Rows deleted	Bathroth	Word VBA	1	10-01-2014 01:40 PM