Help speeding up macro

ivan_fuzz

Board Regular
Joined
Jan 7, 2005
Messages
63
Hello, I have a macro that looks for data in a cell, if there is data, it deletes the entire row. It works fine but it's taking forever to run on a 2000 line spreadsheet. Does anyone know how to speed it up? I've tried turning of the screenupdating but that didn't help much.

Range("D2").Select
Do Until ActiveCell.Offset(0, -3) = ""
If ActiveCell = "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If
Loop

Thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Bruno

Active Member
Joined
Feb 17, 2002
Messages
491
another approach to solve this :
- sort your list on column D
- remove the lines you don't need
- if neccessary, sort the list again on column A (or another column)
 
L

Legacy 12630

Guest
Columns(1).SpecialCells(xlCellTypeConstants, 23).EntireRow.Delete
Columns(1).SpecialCells(xlCellTypeFormulas, 23).EntireRow.Delete
 

ivan_fuzz

Board Regular
Joined
Jan 7, 2005
Messages
63
Thanks for the advice. I resorted it first and that does help a little.

I'm not sure how to incorporate Ponsonby's code though. I've never used that syntax.
 

Forum statistics

Threads
1,147,510
Messages
5,741,577
Members
423,668
Latest member
Audorin

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