Delete Row based on Search Key VBA
I am trying to delete every row in which the value "X" is found in column B using VBA. However, I'm having three problems:
- I can't get my VBA code to move down from the active cell to the next cell (B3) using cells.find method (see code below)
- My code does not delete the entire row where the value "X" is found in column B
- The amount of Data in Column B can vary: it can end at B10 today, or B100 tomorrow (see screen shot below)
Any assistance will be greatly appreciated.
Sub RemoveRows()
Dim strLookFor As String
Dim strRow As String
Worksheets("Sheet1").Range("B2").Activate
strLookFor = "X"
strRow = Range("B2").Address
Do Until ActiveCell.Value = ""
MsgBox (ActiveCell.Value)
If ActiveCell.Value = strLookFor Then
Rows.Delete (strRow)
End If
strRow = Cells.Find(what:=strLookFor).Address
Loop
MsgBox ("Deleted all rows with value " & strLookFor)
End Sub
Using an AutoFilter
is much more efficient than a range loop
Sub QuickCull()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Sheet1")
Set rng1 = ws.Range(ws.[b2], ws.Cells(Rows.Count, "B").End(xlUp))
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
rng1.AutoFilter Field:=1, Criteria1:="X"
rng1.Offset(1, 0).EntireRow.Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
dim x as long
dim r as long
dim sht as worksheet
set sht = Worksheets("Sheet1")
r = sht.Cells(rows.count,2).end(xlup).row
for x = r to 2 step -1
with sht.cells(x,2)
if .value = "X" then .entirerow.delete
end with
next x
This should work:
Sub DeleteRowsWithX()
maxRow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To maxRow
Do While (StrComp(ActiveSheet.Cells(i, 2).Text, "X", vbTextCompare) = 0)
Rows(i).Select
Selection.Delete Shift:=xlUp
Loop
Next
End Sub