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
|