Remove thousands of rows in Excel 2010 & set maximum row limit in spreadsheet

We have a spreadsheet of 3922 rows that has somehow expanded to include thousands of blank, unused rows with formulas, rendering the spreadsheet almost unusable. The active spreadsheet is from A1 to FA3922, but when I hit CTRL-END, the last cell is now FA1048576. Due to the complexity of conditional formatting, formulas, etc. I don't want to risk copying the data to a new spreadsheet. Instead, I wondered if:
1. Is there a macro or VBA that can be implemented to delete rows 3923 to 1048576.
2. Is there a way to set the maximum number of rows in a spreadsheet to 5000?

Thanks,
Andrea
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Andrea,
The number of rows in a spreadsheet is fixed by Microsoft. With the .xlsx file format, there will be 1,048,576 rows. With the previous .xls file format, there are 65,536 rows.

If you want to delete rows 3923 and beyond, you might use code like:
Sub DeleteMe()
Range(Cells(3923,1), Cells(Rows.Count, 1)).EntireRow.Delete
End Sub

Open in new window

AndreamaryAuthor Commented:
Thanks for the quick response. I tried the macro, and after a couple of minutes got a message saying 'Excel cannot complete this task with available resources...'. Any suggestions on how to best break down the task?
Thanks,
Andrea
Martin LissOlder than dirtCommented:
You could try modifying byundt's code to do it in blocks. (Untested)

Sub DeleteMe()
Cont ROWS = 53000
Dim lngCnt As Long
For lngCnt = 1 To 20
    Range(Cells(3923,1), Cells(ROWS, 1)).EntireRow.Delete
Next
End Sub

Open in new window

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

byundtMechanical EngineerCommented:
Andrea,

Martin Liss' code ought to address the problem with insufficient resources. If it does not, we really need to see your actual workbook, so we can test our code on your real problem.

Is it possible to post your workbook?

Brad
[ fanpages ]IT Services ConsultantCommented:
You may also like to try switching the MS-Excel Application (Workbook) Calculation Mode to 'Manual' before starting the deletion, & then reverting to 'Automatic' (if that was the previous setting) when the deletion has completed, as this will speed up the removal of the rows considerably.

This may also help with the lack of resources with the code provided by Brad or, indeed, simply deleting the rows manually (as you attempted initially).
byundtMechanical EngineerCommented:
Another thing you might try is saving the file as .xls. Those files have only 65,536 rows.

If the save to .xls format succeeds, you would then relaunch Excel, open the .xls file and save it as .xlsx (or .xlsm). You may then be able to delete the extra rows of formulas without getting a lack of resources warning message.
[ fanpages ]IT Services ConsultantCommented:
^ Plus saving in Excel Binary workbook (*.xlsb) format may* reduce the overall size of the file &, as a consequence, the amount of resources (Random Access Memory [RAM]) required to process the deletion of the rows.

* In some cases, saving in ".xlsb" format could increase the size of the workbook so always make a copy of the workbook (& store separately) so that if you re-save in a different format you can return to the previously saved format, if desired.
AndreamaryAuthor Commented:
Thanks for all your suggestions (sorry not to have responded sooner, but I've been away).

I tried Martin Liss' formula, but got an error message as follows: "Compile error: Sub or function not defined", and in the macro, the word "Rows' in line 2 is highlighted.

I tried changing the formula calculation mode from automatic to manual, but it did not resolve it.

I also tried saving as an .xls, but it still did not have the resources to delete to rows 65,536.

I'm hoping that Martin's formula could be a solution once the compile error can be resolved...

Thanks!
Andrea
[ fanpages ]IT Services ConsultantCommented:
I also tried saving as an .xls, but it still did not have the resources to delete to rows 65,536.

How about Excel Binary workbook (*.xlsb) format; did you look at that?
byundtMechanical EngineerCommented:
Andrea,
There was a typo in line 2 of Martin Liss' code. It should have been Const rather than Cont.

Since saving as .xls didn't work, you may need to delete smaller blocks of code. I modified his code accordingly:
Sub DeleteMe()
Const Rows = 3000      'If not enough resources, try a smaller number
Dim lngCnt As Long, n As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
n = (ActiveSheet.Rows.Count - 3923) / Rows + 1
For lngCnt = 1 To n
    Range(Cells(3923, 1), Cells(3923 + Rows, 1)).EntireRow.Delete
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Thanks, byundt, the revised macro worked!

Fanpages, I tried saving as a binary workbook and it didn't reduce the file size or resolve the resources issue, unfortunately.

Thanks again to everyone for their contributions. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.