Adding a loop to conditional delete code

[maw via OfficeKB.com]

Hello all,

I have the following code:

Sub DelOver16()

If ActiveCell.Value >= 16 Then ActiveCell.EntireRow.Delete

End Sub

What I need to do is have the code loop through cells F2:F10000 and delete
entire rows that have a value of >=16.

How would I do that?

Many thanks in advance

Mark

 

[SteveW]

Not the only way

Sub DelOver16()
Dim myRange As Range
Set myRange = ActiveWindow.RangeSelection
'
For Each c In myRange.Cells
If ActiveCell.Value >= 16 Then ActiveCell.EntireRow.Delete
Next
End Sub

Select required range, run macro

Steve

 

[Alan]

Sub DelOver16()
Dim Rng, Cell
Set Rng = Range("A1:A10000")
For Each Cell In Rng
If Cell.Value >= 16 Then Cell.EntireRow.Delete
Next
End Sub

Regards,
Alan.

 

[maw via OfficeKB.com]

Wow,

Thank you both very much, such a quick response.

Both solutions seem to work perfectly,

Thanks again!

Mark

 

[SteveW]

Use Alans his works

mine should have said
For Each c In myRange.Cells
If c.Value >= 16 Then c.EntireRow.Delete
Next

Steve

 

[maw via OfficeKB.com]

Thanks Steve,

I did make that little amendment to yours and it works fine

Thanks again guys

Mark

 

[Bernie Deitrick]

You could speed that up by sorting first, and not looping:

Sub DelOver16VerB()
Dim myCell As Range
Dim myRow As Long
Range("F2:F10000").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes
myRow = Application.Match(16, Range("F2:F10000"))
Set myCell = Range("F2:F10000").Cells(myRow + 1)
Range(myCell, myCell.End(xlDown)).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

You should note that if you have consecutive cells with a value greater than 16, the second cell's
row will not be deleted using this method.

The prefered step through method is to start at the bottom and work up.

For myRow = 10000 to 2 Step - 1
If Cells(myRow,6).Value >= 16 Then Cells(myRow,6).EntireRow.Delete
Next myRow

Still slower than sorting first, but it will get all the values, unlike the first method.

HTH,
Bernie
MS Excel MVP

 

[SteveW]

Neat.

Now put the other rows back where they were

Steve

You could speed that up by sorting first, and not looping:

Sub DelOver16VerB()
Dim myCell As Range
Dim myRow As Long
Range("F2:F10000").Sort Key1:=Range("F2"), Order1:=xlAscending,
Header:=xlYes
myRow = Application.Match(16, Range("F2:F10000"))
Set myCell = Range("F2:F10000").Cells(myRow + 1)
Range(myCell, myCell.End(xlDown)).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry, I missed the >= part...

Sub DelOver16VerC()
Dim myCell As Range
Dim myRow As Long
Range("F2:F10000").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes
If IsError(Application.Match(16, Range("F2:F10000"), False)) Then
myRow = Application.Match(16, Range("F2:F10000")) + 1
Else
myRow = Application.Match(16, Range("F2:F10000"), False)
End If
Set myCell = Range("F2:F10000").Cells(myRow)
Range(myCell, myCell.End(xlDown)).EntireRow.Delete
End Sub


--
HTH,
Bernie
MS Excel MVP

You could speed that up by sorting first, and not looping:

Sub DelOver16VerB()
Dim myCell As Range
Dim myRow As Long
Range("F2:F10000").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes
myRow = Application.Match(16, Range("F2:F10000"))
Set myCell = Range("F2:F10000").Cells(myRow + 1)
Range(myCell, myCell.End(xlDown)).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[maw via OfficeKB.com]

That's cool!

Thanks Bernie

If I wanted to change the value to say less than 10 how would I do that -
these values are the ages of children rounded to the nearest whole year and
sometimes I need to select those aged under a given age such as 16, 10, 5 etc

Thanks again

Mark

 

[maw via OfficeKB.com]

It's OK, I see

Just change the value '16' to 10 or 5 or whatever?

 

[Bernie Deitrick]

Steve,

Putting them back is trivial. If it is important, you simply re-sort on another column, or if there
isn't a column to base the sort on, you add another column through with the original row number to
allow re-sorting. But the speed gain can be up to 1000 times, given testing that I have done
previously.

Bernie
MS Excel MVP


Neat.

Now put the other rows back where they were

Steve

 

[SteveW]

Good point, I remember something about that now.
Steve

 

[Bernie Deitrick]

Yes, you've got the idea. You could also use an inputbox so that you don't have to change the code.

BUT, you could use data filters to show parts of the list instead of deleting the rows, so that you
can change your view at any time. That would actually be a much better practice.

HTH,
Bernie
MS Excel MVP

 

[maw via OfficeKB.com]

I see, it sorts the age only not the entire row :-(

Howwever, Alans solution works fine

Thanks!

 

[SteveW]

There was a in the post

Anyway it now seems filtering would be a better option for the OP as other
age sets maybe needed

Regarding speed - it takes you back to the old days of data processing -
when a little thought was needed.
these days the usual approach is to find/stumble across something that
works then use it on large sets of data in just the same way.

Steve

 

[Bernie Deitrick]

That was my mistake - I wronly assumed that you only had data in column F. To fix that, simply
change

Range("F2:F10000").Sort .....
to
Range("F2:F10000").CurrentRegion.Sort .....

Sorry about that,
Bernie
MS Excel MVP

 

[maw via OfficeKB.com]

Still trying to get my head round this as its far beyond my usual Excel
capability!

Maybe I should explain a little more. I'm a researcher for a childrens
charity and I regularly have to construct samples using data pulled from our
SQL Server into an Excel file. Usually the sample has an upper age limit
(sometimes a lower one too). Usually samples aren't so big so I can manually
filter and use a macro to de-dupe. But this one is quite large. Each row has
a unique ID such as 123456, a unique child ID such as 8765, a UK postcode
such as YO10 5JL and then the child's age.

So my data looks like:

123456 | 8765 | YO10 5JL | 24 |
333568 | 1236 | E54 6KG | 13 |
436543 | 4690 | W34 7BM | 16 |


What I need to do is delete all rows where the child age is 16 or over but I
do need to keep the rows in order so that in the above example I would be
left with:

333568 | 1236 | E54 6KG | 13 |

Hope this makes sense and sorry for any (my) confusuion,

Thanks again,

Mark

 

[maw via OfficeKB.com]

Thank you very much Bernie

everyones help has been very much appreciated and I have actually learnt
something new.

What an excellent forum

Thanks again

Mark

Bernie Deitrick wrote: