Multiple selections

dedelkoo

New Member
Joined
Mar 19, 2010
Messages
28
Hi All:

I'm trying to code a macro to delete irrelevant data and the for loop I'm using takes considerable time; I'm wondering if selecting all the irrelevant rows and then deleting would be faster than deleting each row as its found.

Currently the code I have is:

For x = LR To 2 Step -1
If Range("B" & x).Value = "" Then
Rows(x).Select
Selection.Delete shift:=xlUp
End If
Next x
(LR is a line count variable)
This code works but is slow as the sheet usually has in excess of 10,000 rows. I'm hoping there's syntax I could use in the loop to keep adding to my selected rows; then once the for loop is done have one line that deletes the entire selection. Any thoughts?

Many thanks.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
I have no idea but an easy way to make your code faster is by using
Application.ScreenUpdating

So your code would be
Code:
Application.ScreenUpdating = False
For x = LR To 2 Step -1
If Range("B" & x).Value = "" Then
Rows(x).Select
Selection.Delete shift:=xlUp
End If
Next x
Application.ScreenUpdating = True
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Moreover, it would be a good idea if you removed the .Select function.
It slows down your code alot.

So instead of
Code:
Rows(x).Select
Selection.Delete shift:=xlUp

Use
Code:
Rows(x).Delete shift:=xlUp
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,424
Messages
5,831,556
Members
430,075
Latest member
Tuturino

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