asked on

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

byundt

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

Andreamary

ASKER

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 Liss

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

byundt

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 ]

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).

byundt

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 ]

^ 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.

Andreamary

ASKER

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 ]

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?

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

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. :-)

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

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