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

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

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

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

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

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

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

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

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