Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default Adding a loop to conditional delete code

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


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




--
Steve (3)
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 492
Default Adding a loop to conditional delete code

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



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default Adding a loop to conditional delete code

Use Alans his works :)

mine should have said
For Each c In myRange.Cells
If c.Value = 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)


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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



  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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



  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default Adding a loop to conditional delete code

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:=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






--
Steve (3)
  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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
...
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







  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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" wrote in message news:op.tebvu5srevjsnp@enigma03...
Neat.

Now put the other rows back where they were :)

Steve



  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default Adding a loop to conditional delete code

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 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






--
Steve (3)
  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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





  #16   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

  #17   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default Adding a loop to conditional delete code

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

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 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" wrote in message
news:op.tebvu5srevjsnp@enigma03...
Neat.

Now put the other rows back where they were :)

Steve






--
Steve (3)
  #18   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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



  #19   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

  #20   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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



  #21   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default Adding a loop to conditional delete code

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



  #22   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default Adding a loop to conditional delete code

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit this macro code to loop and end GarToms Excel Discussion (Misc queries) 1 January 27th 06 01:50 PM
Code to delete rows and column cells that have formulas in. GarToms Excel Worksheet Functions 1 January 18th 06 02:04 PM
bar code help, adding asterix to reference R D S Excel Discussion (Misc queries) 1 January 17th 05 07:05 PM
Stubborn toolbars in Excel 007 Excel Discussion (Misc queries) 9 December 11th 04 03:02 PM
VBA Newbie: Help with Do Loop code Carl Excel Discussion (Misc queries) 3 December 2nd 04 08:04 PM


All times are GMT +1. The time now is 04:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"