Excel million rows; can't delete with delete+save method

[jebenexcel]

Hello guys,

As I said in the title, I have a spreadsheet with a million unneeded rows and I cannot delete them with the select rows, delete rows, save spreadsheet method.
What other methods are there?

 

[Kamolga]

Is it due to protection or a macro?

 

[Trevor G]

Not total sure what you are asking! Do you mean you have a version of Excel 2010 or higher which provides 1048576 rows on a sheet and you don't want to see unused rows, the only thing I can suggest is to hide the rows. Older versions of Excel provides 65536 rows.

 

[jebenexcel]

The sheet on which I wish to delete rows is unprotected, however others are protected. Could this somehow be the reason?

The version is 2016; there are 1048576 rows (roughly a million). Hiding the rows is a possibility, but leaves me with the problem of the scroll bar being useless (1000 rows are used out of a million, so you have no scroll resolution).

 

[Yongle]

Give the rows below your used range a zero row height - scroll still works

Sub ShortRows()
With ActiveSheet    
    .Range("A1").Select
    .Range("A2", Range("A" & Rows.Count)).RowHeight = 0
    .UsedRange.RowHeight = 15
End With
End Sub

 

[Kamolga]

When they are deleted you are always able to see them, that is logical.

I hide them personnally, so CTRL down works fine even with empty cells (and no scroll if you did not went down before hiding) but I think this solution is what you are looking for (no hide, no protection):

-Get on your sheet
-then devlopper tab (file/options/customize ribbon if you don't already have it)
-click properties.
-You have a scroll area were you can type A1:T1000 for example
-Press enter

Now you can not scroll out of the specified area anymore and see empty rows and columns

 

[Yongle]

similar to what @Kamolga is suggesting

Sub SheetLimits()
    With ActiveSheet
        .ScrollArea = .UsedRange.Address
    End With
End Sub

 

[offthelip]

I have had the problem of the scroll bar being unusable and found i could reset it by running this macro:

Sub ResetRng()    
' Keyboard Shortcut: none
    ' resets the used range of the activesheet
    ActiveSheet.UsedRange
End Sub

 

[jebenexcel]

The Subs don't help;
Limiting the scroll area still leaves the scroll bar with next to no resolution.

 

[Yongle]

I still do not understand what you are trying to achieve

this I understand...
- You are using 1000 rows approx
- The worksheet contains over 1 million rows (which is fixed and cannot be changed)
- It is not possible to delete rows - deleted rows are simply "moved" to the bottom of the worksheet (the total number of rows remains unchanged)
- Most users have many unused rows at the bottom of the worksheet

1. Please ignore all previous posts and go back to your original request - What is it that you want to do that is not working for you?
- please explain exactly what you are trying to do and what is preventing you from doing it

2. In old Excel you had over 60,000 rows unused - Why is it a bigger problem to have more rows unused in Excel2016?

3. What do you mean by this? ... "the scroll bar is left with next to no resolution"
- explain why it is a problem