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]

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

Yes this works perfectly. If this code does not work, it means you have a value or property (such has conditional format, font color,...) on your last rows...wich is very bad for file size and execution speed. You should move it to another worksheet/workbook if you can't get rid of that.

 

[jebenexcel]

1.) I would like to have an Excel workbook with a worksheet that does not contain the maximum number of rows below the actually used part of the worksheet. Any method to remove said unneeded rows past row 1000 has not yielded results.
I have no idea what is preventing me from achieving this. The problem is not whether those rows are used or not, the problem is that they exist while not used at all even after saving. The document is always at max open rows and the scroll bar is proportionately (not) sensitive. This leads me to point 3.
2.) I did not talk about the 60k or 1000k max row count, though my problem with a 60k max workbook would be less pronounced.
3.)The problem is that if I am using only 1000 rows out of 1M, and I don't feel like scrolling with the mousewheel or the arrows at the top or the bottom of the scroll bar, I would need to have the accuracy to move the scroll bar by one per mille of the scroll bar to not entirely miss the used part of the worksheet.
At 60k max rows, my mouse accuracy would have to be 1/60 of the possible. Therefore, I would like my scroll bar to represent one thousand rows of the worksheet, and a relative move of the scroll bar by roughly one quarter of its length to represent a jump of roughly 250 rows. Limiting the scroll area through developer tools does not achieve this, as the sensitivity of the scroll bar is as if I had one million rows in the worksheet; moving it down only results in the screen having a seizure between the final few lines of the scroll area.

Basically, I want to have a usable scroll bar, but this is prevented by the number of existing rows that I cannot delete by any means here mentioned.

The worksheet is a list of contracts, and one of the users has managed to ctrl-down and save, therefore for ever formatting all possible rows with something and rendering the scroll bar useless. It is useful to have the ability to scroll easily through contract lists and I would like to regain this ability. However, just copying the worksheet into a new workbook is not desirable due to there being more worksheets in the workbook that are interlinked with cell links and macros.

 

[jebenexcel]

@Kamolga there is conditional formatting in this worksheet. It used to exist only in the work area of the first 1k rows, but as the author of the workbook did not scroll lock it, it has been extended to the bottom of the worksheet. As I said in my reply one above this one, one of the users of the workbook managed to ctrl-down to the bottom and save the file. Is there a way to undo this?

 

[Fluff]

Simply change the applies to range for the conditional formatting

 

[Kamolga]

@Kamolga there is conditional formatting in this worksheet. It used to exist only in the work area of the first 1k rows, but as the author of the workbook did not scroll lock it, it has been extended to the bottom of the worksheet. As I said in my reply one above this one, one of the users of the workbook managed to ctrl-down to the bottom and save the file. Is there a way to undo this?

you can change this in manage rules of the conditional formating. Then launch the used range macro and the top bottom of your scroll bar will be at 1000 row. You can then decide on hiding the rows or scroll locking

 

[Zenwood]

Does Ctrl + End got to row 1,000 or row 1,000,000?

 

[Yongle]

Something that should work for you:
- add a new worksheet
- select ONLY your 1000 rows and paste to the new sheet
- the conditional formatting will finish at row 1000