Deleting rows that contain a certain value in column A

[Bishop]

I have a spreadsheet sorted by column A. I need a procedure that will find
every cell with a specific value (they will all be grouped together because
of the sort) in column A and delete every row (ever how many it is) that has
that value in column A. Then I need the remaining data shifted up.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste the code below in and run
it. It currently looks for the number 3 in column A so change to suit. If
your looking for a text value it must be in quotes "Myvalue"

Sub delete_Me2()
Dim copyrange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = 3 Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike

 

[Gary''s Student]

Suppose we want to remove "misery":

Sub StayHappy()
Dim n As Long
Dim i As Long
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Value = "misery" Then
Rows(i).Delete
End If
Next
End Sub

 

[Rick Rothstein]

How about removing "misery" something like this non-looping way instead?

Sub StayHappy()
Dim Word As String
Word = "misery"
Columns("A").AutoFilter Criteria1:=Word, Field:=1, VisibleDropDown:=False
Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).Offset( _
Abs(Range("A1").Value <> Word)).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub