Loop (repeat) macro 100 times- Delete 1 Million rows

[bloodybrit90]

Hi guys, I need to repeat the below code 100 times each time I click it. I have a spreadsheet with over 1,000,000 rows and throusands of columns. I am trying to delet the rows out. When I attempt to delet all the rows at once I get a message stating too much info is selected. If you know a better way, please share.

Sub Delete_empty()
'
' Delete_empty Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Rows("91:219").Select
Range("D91").Activate
Selection.Delete Shift:=xlUp
End Sub

 

[VoG]

Try without selecting

Sub Delete_empty()
'
' Delete_empty Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Rows("91:219").Delete
End Sub

 

[bloodybrit90]

The issue is this takes forever to delete all the rows. Only 128 rows are being deleted at once. Am I missing something? Sorry I am new to VBA.

 

[VoG]

Instead of "91:219" enter the row numbers that you want to delete.

 

[Rick Rothstein]

The issue is this takes forever to delete all the rows. Only 128 rows are being deleted at once. Am I missing something? Sorry I am new to VBA.

Let's go back to Step #1... why are you deleting one million rows in the first place? Do you have data below them that you are trying to bring up to the top? Or are you just attempting to clear the worksheet? Or something else? (My thought is you might need to be Clear'ing the one million rows and cut/pasting any remaining data for the first case or simply Clear'ing all the cells outright.)

 

[bloodybrit90]

IT worked Thanks VoG.

 

[bloodybrit90]

I inherited this worksheet and it was running slow. THere was no data past cell 91, so I am not sure why there were so many rows in the first place. By deleting the unsued rows I thought the spreadsheet will run better. Thanks for all the help.

 

[VoG]

That is a kind of "corruption" issue. You could try MakeLastCell Lastcell, Reset Last Used Cell which in my experience always works.