Deleting multiple rows at once in VBA

samfolds

Board Regular
Joined
Jul 2, 2009
Messages
191
Hi,

I need to make a macro that will delete a row when a condition is met. The problem is, I got more or less 2000 rows per worksheet to delete and it's very time consuming to delete them one by one.

So, is there a way to "store in a variable" all the rows I need to delete and then delete them all at once (like using HOLD Control + click rows and then delete) ?

Thanks!

Samfolds
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
How about assign a flag for rows you want to keep, filter out those rows and then use specialcells(xlvisible).rows.delete?

You may need to check the syntax for above but that I think would be quickest way.
 
Upvote 0
Dim deleteRng as Range

Then set deleteRng = the first row you want to delete. Subsequent rows can be added by using:
deleteRng = Application.Union(deleteRng,Range("NextRowYouWantToDelete"))

continue to add all rows to be deleted then

deleteRng.Delete will remove them all in one shot
 
Upvote 0
Hi JoeMo,

Can you tell me why this is failing on the IF statement? I have numbers 1 to 31 running down column A and I'm trying to use your solution to Samfolds problem:
Rich (BB code):
Sub sort_match()
Dim delRange As Range
Dim i As Long
i = Range("A" & Rows.Count).End(xlUp).Row
Do
If WorksheetFunction.Mod(Range("A" & i).Value, 2) = 1 Then
    delRange = Application.Union(delRange, Range("A" & i).EntireRow)
End If
i = i - 1
Loop Until i = 1
MsgBox "Range ready for deletion"
delRange.Delete
End Sub
 
Upvote 0
Using Excel2007 - in VBA, it doesn't seem to recognise .Mod after WorksheetFunction hence failing...
 
Upvote 0
I still use 03; but i think the following syntax would work...

Code:
 If Range("A" & i).Value Mod 2 = 1 Then
 
Upvote 0
Thanks - also realised I need to define my first instance of delRange (e.g. Set delRange = )
 
Upvote 0
You would also need to set delrange before starting the loop; something like

Code:
Set delRange = Range("A" & ActiveSheet.Rows.Count)

Then, another change;

Code:
delRange = Application.Union(delRange, Range("A" & i).EntireRow)

To...

Code:
    Set delRange = Union(delRange, Range("A" & i))

Then

Code:
delRange.Delete

To...

Code:
delRange.EntireRow.Delete


***was a little slow to post; looks like you've got it now.
 
Upvote 0
So now I have this which is still not working:
Rich (BB code):
Sub sort_match()
Dim delRange As Range
Dim i As Long
i = Range("A" & Rows.Count).End(xlUp).Row
Set delRange = Range("A" & i)
Do
    If Range("A" & i) Mod 2 = 1 Then
         SET delRange = Application.Union(delRange, Range("A" & i))
    End If
    i = i - 1
Loop Until i = 1
delRange.EntireRow.Delete
End Sub

EDIT: missed SET in red above out which is why it didn't work. Working now, thank you
 
Upvote 0
Jack, I use this technique quite a lot for my deletion routines, this line of code you have seems to include a row of the actual original data in the delete range:
Rich (BB code):
Set delRange = Range("A" & i)

I typically do the same LastRow thing, but then I seed the delRange with a cell outside the data range, most commonly for me:
Rich (BB code):
Dim LR as Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Set delRange = Range("A" & Rw + 10)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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