Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit this macro code to loop and end | Excel Discussion (Misc queries) | |||
Code to delete rows and column cells that have formulas in. | Excel Worksheet Functions | |||
bar code help, adding asterix to reference | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) |