| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a row when a condition is met using VBA
I need help with VBA code for deleting an enitre row when an condition is
met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
Give this a whirl...
Sub test() Dim Cell As Range Dim AllFound As Range Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For Each Cell In Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Select Case Cell.Value Case 3, 5, 10, 11, 12, 16, 97, 413, 414, 424, 431 Case Else If AllFound Is Nothing Then Set AllFound = Cell Else Set AllFound = Union(Cell, AllFound) End If End Select Next If Not AllFound Is Nothing Then AllFound.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
This ran okay when tested...
Sub DeleteRow() Dim varArray As Variant Dim varItems As Variant Dim lngOffset As Long Dim varVal As Variant Dim blnMatch As Boolean Range("L2").Select varArray = Array(3, 5, 10, 11, 12, 16, 97, 413, 424, 431) varItems = varArray varVal = ActiveCell.Value Application.ScreenUpdating = False Do Until varVal = "" Application.StatusBar = "Evaluating row " & lngOffset _ & ", please wait..." For Each varItems In varArray If varItems = varVal Then blnMatch = True Exit For End If Next varItems If blnMatch Then ActiveCell.Offset(lngOffset). _ EntireRow.Delete lngOffset = lngOffset + 1 varVal = ActiveCell.Offset(lngOffset).Value Loop With Application .StatusBar = False .ScreenUpdating = True End With End Sub -- Kevin Backmann "Jim Thomlinson" wrote: Give this a whirl... Sub test() Dim Cell As Range Dim AllFound As Range Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For Each Cell In Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Select Case Cell.Value Case 3, 5, 10, 11, 12, 16, 97, 413, 414, 424, 431 Case Else If AllFound Is Nothing Then Set AllFound = Cell Else Set AllFound = Union(Cell, AllFound) End If End Select Next If Not AllFound Is Nothing Then AllFound.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
That was it. Works like a champ. Thank you so much I have been working on
this for a couple of days I'm trying to learn VBA. Thanks again. -- Thanks, Scott "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
Hey Kevin. Just a heads up:
You must not have had two adjacent rows that needed to be deleted because your code skips over the 2nd row each timer. Put 3 in L2:L15 and run it to see what I mean. -- Regards, Tom Ogilvy "Kevin B" wrote: This ran okay when tested... Sub DeleteRow() Dim varArray As Variant Dim varItems As Variant Dim lngOffset As Long Dim varVal As Variant Dim blnMatch As Boolean Range("L2").Select varArray = Array(3, 5, 10, 11, 12, 16, 97, 413, 424, 431) varItems = varArray varVal = ActiveCell.Value Application.ScreenUpdating = False Do Until varVal = "" Application.StatusBar = "Evaluating row " & lngOffset _ & ", please wait..." For Each varItems In varArray If varItems = varVal Then blnMatch = True Exit For End If Next varItems If blnMatch Then ActiveCell.Offset(lngOffset). _ EntireRow.Delete lngOffset = lngOffset + 1 varVal = ActiveCell.Offset(lngOffset).Value Loop With Application .StatusBar = False .ScreenUpdating = True End With End Sub -- Kevin Backmann "Jim Thomlinson" wrote: Give this a whirl... Sub test() Dim Cell As Range Dim AllFound As Range Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For Each Cell In Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Select Case Cell.Value Case 3, 5, 10, 11, 12, 16, 97, 413, 414, 424, 431 Case Else If AllFound Is Nothing Then Set AllFound = Cell Else Set AllFound = Union(Cell, AllFound) End If End Select Next If Not AllFound Is Nothing Then AllFound.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
Are you sure that works? From the original posted code if the cell in column
L is not in that list then it then the row should be deleted (best as I can tell). Your code deletes the values in the list, or at least some of them. When the row is deleted your counter is now off by a row and you are deleting the wrong rows... If you want to delete rows using a counter you really want to move upwards from the bottom of the list not down from the top so that deleting rows does not throw you off. -- HTH... Jim Thomlinson "Kevin B" wrote: This ran okay when tested... Sub DeleteRow() Dim varArray As Variant Dim varItems As Variant Dim lngOffset As Long Dim varVal As Variant Dim blnMatch As Boolean Range("L2").Select varArray = Array(3, 5, 10, 11, 12, 16, 97, 413, 424, 431) varItems = varArray varVal = ActiveCell.Value Application.ScreenUpdating = False Do Until varVal = "" Application.StatusBar = "Evaluating row " & lngOffset _ & ", please wait..." For Each varItems In varArray If varItems = varVal Then blnMatch = True Exit For End If Next varItems If blnMatch Then ActiveCell.Offset(lngOffset). _ EntireRow.Delete lngOffset = lngOffset + 1 varVal = ActiveCell.Offset(lngOffset).Value Loop With Application .StatusBar = False .ScreenUpdating = True End With End Sub -- Kevin Backmann "Jim Thomlinson" wrote: Give this a whirl... Sub test() Dim Cell As Range Dim AllFound As Range Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For Each Cell In Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Select Case Cell.Value Case 3, 5, 10, 11, 12, 16, 97, 413, 414, 424, 431 Case Else If AllFound Is Nothing Then Set AllFound = Cell Else Set AllFound = Union(Cell, AllFound) End If End Select Next If Not AllFound Is Nothing Then AllFound.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
Ooops.
You're right (both Tom & Jim), I didn't think that one through to the end. Thanks for the heads up, it keeps me honest, teachable, and dare I say it, humble. Thanks... -- Kevin Backmann "Tom Ogilvy" wrote: Hey Kevin. Just a heads up: You must not have had two adjacent rows that needed to be deleted because your code skips over the 2nd row each timer. Put 3 in L2:L15 and run it to see what I mean. -- Regards, Tom Ogilvy "Kevin B" wrote: This ran okay when tested... Sub DeleteRow() Dim varArray As Variant Dim varItems As Variant Dim lngOffset As Long Dim varVal As Variant Dim blnMatch As Boolean Range("L2").Select varArray = Array(3, 5, 10, 11, 12, 16, 97, 413, 424, 431) varItems = varArray varVal = ActiveCell.Value Application.ScreenUpdating = False Do Until varVal = "" Application.StatusBar = "Evaluating row " & lngOffset _ & ", please wait..." For Each varItems In varArray If varItems = varVal Then blnMatch = True Exit For End If Next varItems If blnMatch Then ActiveCell.Offset(lngOffset). _ EntireRow.Delete lngOffset = lngOffset + 1 varVal = ActiveCell.Offset(lngOffset).Value Loop With Application .StatusBar = False .ScreenUpdating = True End With End Sub -- Kevin Backmann "Jim Thomlinson" wrote: Give this a whirl... Sub test() Dim Cell As Range Dim AllFound As Range Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For Each Cell In Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Select Case Cell.Value Case 3, 5, 10, 11, 12, 16, 97, 413, 414, 424, 431 Case Else If AllFound Is Nothing Then Set AllFound = Cell Else Set AllFound = Union(Cell, AllFound) End If End Select Next If Not AllFound Is Nothing Then AllFound.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
I have the same VBA code to sort out my worksheet. The code that sort the
worksheet works. The only problem is that after the code sorted it out, residuals such as the headings and other text remains. Can anyone give me the code to eliminate these rows of text. Thank you. -- scaps "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete a row when a condition is met using VBA
We have all posted wrong answers (except Tom he just points them out <g).
You will learn more from your wrong answers than you will from the correct ones. Don't let your ego get in the way of learning. Just keep posting and remember it is not personal (it just feels that way sometimes). -- HTH... Jim Thomlinson "Kevin B" wrote: Ooops. You're right (both Tom & Jim), I didn't think that one through to the end. Thanks for the heads up, it keeps me honest, teachable, and dare I say it, humble. Thanks... -- Kevin Backmann "Tom Ogilvy" wrote: Hey Kevin. Just a heads up: You must not have had two adjacent rows that needed to be deleted because your code skips over the 2nd row each timer. Put 3 in L2:L15 and run it to see what I mean. -- Regards, Tom Ogilvy "Kevin B" wrote: This ran okay when tested... Sub DeleteRow() Dim varArray As Variant Dim varItems As Variant Dim lngOffset As Long Dim varVal As Variant Dim blnMatch As Boolean Range("L2").Select varArray = Array(3, 5, 10, 11, 12, 16, 97, 413, 424, 431) varItems = varArray varVal = ActiveCell.Value Application.ScreenUpdating = False Do Until varVal = "" Application.StatusBar = "Evaluating row " & lngOffset _ & ", please wait..." For Each varItems In varArray If varItems = varVal Then blnMatch = True Exit For End If Next varItems If blnMatch Then ActiveCell.Offset(lngOffset). _ EntireRow.Delete lngOffset = lngOffset + 1 varVal = ActiveCell.Offset(lngOffset).Value Loop With Application .StatusBar = False .ScreenUpdating = True End With End Sub -- Kevin Backmann "Jim Thomlinson" wrote: Give this a whirl... Sub test() Dim Cell As Range Dim AllFound As Range Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For Each Cell In Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Select Case Cell.Value Case 3, 5, 10, 11, 12, 16, 97, 413, 414, 424, 431 Case Else If AllFound Is Nothing Then Set AllFound = Cell Else Set AllFound = Union(Cell, AllFound) End If End Select Next If Not AllFound Is Nothing Then AllFound.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "SBoostrom" wrote: I need help with VBA code for deleting an enitre row when an condition is met. This is what I have so far. Range("A1:T1677").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Dim Cell As Range For Each Cell In Range("L2", Range("L5000").Address) If Cell < 3 Or Cell < 5 Or Cell < 10 Or Cell < 11 Or Cell < 12 Or Cell < 16 Or Cell < 97 Or Cell < 413 Or Cell < 414 Or Cell < 424 Or Cell < 431 Then EntireRow.Delete End If Next End Sub This is I'm sure not even close but all help os appreciated. -- Thanks, Scott |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete Macro with OR/ELSE condition | Excel Discussion (Misc queries) | |||
| Delete Row with condition | Excel Discussion (Misc queries) | |||
| macro to delete on a condition | Excel Programming | |||
| Can I delete an entire row if condition is not met? | Excel Worksheet Functions | |||
| delete rows with certain condition | Excel Programming | |||
Linear Mode
