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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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...
 

excelquest23

Board Regular
Joined
Apr 8, 2006
Messages
58
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.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
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:
 

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,540
Latest member
Nereus A

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
Top