Adding a loop to conditional delete code

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

Not the only way

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

Select required range, run macro

Steve


On Tue, 15 Aug 2006 13:00:30 +0100, maw via OfficeKB.com <u12713@uwe> =

wrote:

> Hello all,
>
> I have the following code:
>
> Sub DelOver16()
>
> If ActiveCell.Value >=3D 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 >=3D16.
>
> How would I do that?
>
> Many thanks in advance
>
> Mark
>



-- =

Steve (3)

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" <u12713@uwe> wrote in message
news:64cdc9dbc43e6@uwe...
> 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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>

Wow,

Thank you both very much, such a quick response.

Both solutions seem to work perfectly,

Thanks again!

Mark

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

Use Alans his works

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

Steve


On Tue, 15 Aug 2006 13:42:32 +0100, maw via OfficeKB.com <u12713@uwe> =

wrote:

> Wow,
>
> Thank you both very much, such a quick response.
>
> Both solutions seem to work perfectly,
>
> Thanks again!
>
> Mark
>



-- =

Steve (3)

Thanks Steve,

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

Thanks again guys

Mark

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

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" <u12713@uwe> wrote in message news:64cdc9dbc43e6@uwe...
> 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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>

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


"maw via OfficeKB.com" <u12713@uwe> wrote in message news:64ce9464e110e@uwe...
> Thanks Steve,
>
> I did make that little amendment to yours and it works fine :-)
>
> Thanks again guys
>
> Mark
>
> --
> www familyfund org uk
>
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>

Neat.

Now put the other rows back where they were

Steve

On Tue, 15 Aug 2006 14:47:13 +0100, Bernie Deitrick <deitbe consumer dot=
=

org> wrote:

> 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:=3DRange("F2"), Order1:=3DxlAscending, =

> Header:=3DxlYes
> myRow =3D Application.Match(16, Range("F2:F10000"))
> Set myCell =3D Range("F2:F10000").Cells(myRow + 1)
> Range(myCell, myCell.End(xlDown)).EntireRow.Delete
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "maw via OfficeKB.com" <u12713@uwe> wrote in message =

> news:64cdc9dbc43e6@uwe...
>> Hello all,
>>
>> I have the following code:
>>
>> Sub DelOver16()
>>
>> If ActiveCell.Value >=3D 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 >=3D16.
>>
>> How would I do that?
>>
>> Many thanks in advance
>>
>> Mark
>>
>> --
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>>
>
>



-- =

Steve (3)

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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <u12713@uwe> wrote in message news:64cdc9dbc43e6@uwe...
>> 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
>>
>> --
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>>
>
>

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

SteveW wrote:
>Neat.
>

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

It's OK, I see

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

maw wrote:
>That's cool!
>

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

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


"SteveW" <[email protected]> wrote in message news:op.tebvu5srevjsnp@enigma03...
Neat.

Now put the other rows back where they were

Steve

Good point, I remember something about that now.
Steve

On Tue, 15 Aug 2006 14:51:43 +0100, Bernie Deitrick <deitbe consumer dot=
=

org> wrote:

> You should note that if you have consecutive cells with a value greate=
r =

> 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 =3D 10000 to 2 Step - 1
> If Cells(myRow,6).Value >=3D 16 Then =

> Cells(myRow,6).EntireRow.Delete
> Next myRow
>
> Still slower than sorting first, but it will get all the values, unlik=
e =

> the first method.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "maw via OfficeKB.com" <u12713@uwe> wrote in message =

> news:64ce9464e110e@uwe...
>> Thanks Steve,
>>
>> I did make that little amendment to yours and it works fine :-)
>>
>> Thanks again guys
>>
>> Mark
>>
>> --
>> www familyfund org uk
>>
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>>
>
>



-- =

Steve (3)

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" <u12713@uwe> wrote in message news:64ceeac56242a@uwe...
> It's OK, I see
>
> Just change the value '16' to 10 or 5 or whatever?
>
> maw wrote:
>>That's cool!
>>
>
> --
> www familyfund org uk
>
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>

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

Howwever, Alans solution works fine

Thanks!

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

There was a in the post

Anyway it now seems filtering would be a better option for the OP as oth=
er =

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

On Tue, 15 Aug 2006 15:12:09 +0100, Bernie Deitrick <deitbe consumer dot=
=

org> wrote:

> 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 wit=
h =

> 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
>
>
> "SteveW" <[email protected]> wrote in message =

> news:op.tebvu5srevjsnp@enigma03...
> Neat.
>
> Now put the other rows back where they were
>
> Steve
>
>
>



-- =

Steve (3)

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" <u12713@uwe> wrote in message news:64cf01e0b2a24@uwe...
>I see, it sorts the age only not the entire row :-(
>
> Howwever, Alans solution works fine
>
> Thanks!
>
> --
> www familyfund org uk
>
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>

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

SteveW wrote:
>There was a in the post

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

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:

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1

Mark,

If you apply the Data Autofilter and select 16 from the dropdown at the top of the age column, you
will be shown just those rows with 16s.

If you select those cells that are shown and copy them, you can paste them into a new sheet, and
Excel will only paste the rows that were visible when you did the copy. That would keep the rows in
order.

Or we can modify the macro to restore the original order, and allow you to pick the age (or age
range) that you want to see.

HTH,
Bernie
MS Excel MVP


"maw via OfficeKB.com" <u12713@uwe> wrote in message news:64cf4568ba1b8@uwe...
> 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
>
> SteveW wrote:
>>There was a in the post
>
> --
> www familyfund org uk
>
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200608/1
>

Hi Bernie,

That's how I have done this in the past, using the manual autofilters, it was
just that this was the first of a series of larger samples (up to 150,000
rows each) and I thought there must be a better/quicker way of doing it using
a macro,

Thanks

Mark

Bernie Deitrick wrote:
>Mark,
>

--
www familyfund org uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200608/1