deleting thousands of rows at once

excelquest23

Board Regular
Joined
Apr 8, 2006
Messages
58
Hello,

I have a worksheet with over 12,000 rows and I'd like to delete the lower 7,000 rows. Everytime I try to highlight more than 500 rows to delete manually the screen locks up on me. Is there an easier way to delete this many rows at once manually or should I use some kind of macro? If a macro is the way to go, could someone please send me one if they have one handy?

Thanks!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Do these rows contain formulas?

If they do then perhaps Excel is calculating the worksheet?

If it is you could turn of manual calculation via Tools>Options...
 
Upvote 0
Thanks for the quick reply.

Some of the cells that I want to delete do contain formulas but I want to delete those too. I have the manual calculation activated but I don't think that is the problem with the sheet locking up. I think it's because the spreadsheet is so large that it eats up a lot of memory and somehow that prevents it from deleting so many rows. But I'm not really sure what's going on. Why would Excel be running calculations if I have the manual calculation turned while I'm just trying to delete rows?

Thanks again.
 
Upvote 0
Maybe this helps for the memory problem:

Change formulas to values:
Select area - Copy - Paste special - Values.
Then save the worksheet with some other name, maybe.

This makes worksheet smaller

Then delete rows.

Pekka
:eek: :eek: :eek:
 
Upvote 0
If you wanted to do it by macro run the following code.

Code:
Sub DeleteRows()

Dim lastCell As Long

lastCell = ActiveSheet.Range("A65536").End(xlUp).Row

'set your first row to delete i.e. 7000
Rows("7000:" & lastCell).Delete xlUp
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top