Delete Rows using loop (Excel 97/VBA)

Topic

New Reply

I’ve tried to no avail to delete certain rows within a workbook based on specific criteria. I can get it to delete some of the rows but it never deletes all of them. I’ve tried various methods of looping script that I’ve gleemed from here, text books, etc. to no avail. Can someone please help. Thanks!! I’ve attached my code for review.

Sub DeleteRows

Dim i as Integer

For i = 1 To Range (“Impact_Test_Criteria”).cells.count
If Range(“Impact_Test_Criteria”).cells(i) = 1 Then
Range(“Impact_Test_Criteria”).Cells(i).Entirerow.Delete

Else

End if

Next i

End Sub

Reply | Quote

Replies

Eazylee,

When using a loop counter to delete members of a collection, it works more reliably to delete from the end to the beginning:

For i = Range(“Impact_Test_Criteria”).Cells.Count To 1 Step -1 ‘etc.

By the way, does the Impact_Test_Critera range contain more than one column? – if so, that also could lead to a counter problem with your code.

One other thing – you can omit the “Else” statement.

Gary

Reply | Quote

You need to start at the last row and countdown to row 1. In your current example, if rows 1 and 2 qualify for deletion, then when row 1 is deleted row two becomes row one, but your counter has moved on to 2 so that the original row 2 is passed over and not deleted.

Try using

For i = Range (“Impact_Test_Criteria”).cells.count to 1 step -1

and see if that works any better.

Andrew C

Reply | Quote

Thank you both!! You must have been typing at the same time. Your suggestion worked like a charm although I’m going to have to take a moment to understand why. I’ve been up since last night trying to get this one part of my code to work properly. I didn’t think anyone who could help me would be checking the forum on a Sat. but you both definitely blessed me today. Thanks again!!

Reply | Quote

Sorry guys, I’ve joined this after the horse has bolted etcetera but thought the following may be “helpful” in a devils advocate kinda way:
set the following code in say “before_right_click” event
in top of list when right clicked the following code runs pretty smoothly, catching all rows (providing theres no blanks mid range)
Do
if activecell.value = “MyValue” then
activecell.entirerow.delete
else
activecell.offset(1,0).select
end if
loop until activecell.value = “”

Alan
cheshire
UK

Reply | Quote

Hi Alan,

It’s always interesting to see another way of doing things that you might not have thought of.

Two reasons why I’d stick with the For…Next method that was originally posted:

(1) It won’t stop if it hits an empty cell
(2) It’s arguably more efficient because it’s acting upon a range, rather than a selection.

Gary

Reply | Quote

Hi Gary,

There’s no argument about it, you are quite right in saying the For ..next is more efficient when running through a range
The reason I used the do..loop in some of my current “adopted” projects was because up until about a week ago I was ignorant of the fact that I could re-assign the used range of a worksheet using “activesheet.usedrange” and hence for some of the worksheets that were coming my way I had less than 5~10 k rows of data but the used range extended to the 65k row in the worksheet.

Alan

Reply | Quote

Now that the scrum has collapsed, I may as well pile on.

-IF- it fits, using AutoFilter in VBA to delete cells is even faster, see JanKarels’ trick here and my generic version of it here.

And if you are not deleting rows, but performing some other action, the SpecialCells method of narrowing down a range may be even better, as it ignores the non-qualified cells. The SpecialCells Method is the VBA way to do anything available in Goto, Special; it can be one way to further narrow what otherwise would be a very large ActiveSheet.UsedRange.

Reply | Quote