delete every row where value equals user input

[Mitchell_Collen via OfficeKB.com]

Hi all.
The code pasted below works only it will not search and delete every row
equal to user value. I have had to press ctrl+b, then enter value such as
'cardiac' over and over. It will delete the row where there is a cell
containing the value cardiac however, it will not delete all rows where the
row contains cardiac. There are 4000+ rows with cardiac and other words that
need to be deleted off the spreadsheet. I know it must be loop but I don't
have any syntax reference or books. Please help me. Thanks in advance. Misty

Here is the code:

Sub Macro3()
' Keyboard Shortcut: Ctrl+b
Dim userResponse
With Worksheets(1).Range("A1:H100")
userResponse = Application.InputBox("Enter a value where the rows are to be
deleted")
Set c = .Find(userResponse, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
End With
End Sub

 

[Thomas Ramel]

Grüezi Misty

Mitchell_Collen via OfficeKB.com schrieb am 18.10.2007

The code pasted below works only it will not search and delete every row
equal to user value. I have had to press ctrl+b, then enter value such as
'cardiac' over and over. It will delete the row where there is a cell
containing the value cardiac however, it will not delete all rows where the
row contains cardiac. There are 4000+ rows with cardiac and other words that
need to be deleted off the spreadsheet. I know it must be loop but I don't
have any syntax reference or books. Please help me. Thanks in advance. Misty

Maybe you will find the following lines useful (even if the variables are
in German:

Public Sub DelRows(rngSpalte As Range, varValue As Variant)
'© Thomas Ramel / 24.01.2005
'Funktion zum Löschen ganzer Zeilen eines Tabellenblattes unter
'Berücksichtigung von Kriterien
'Bedingung: nicht mehr als 8125 unzusammenhängende Bereiche als Ergebnis
'Die Funktion kann nur von VBA aufgerufen werden *nicht* in einer Zelle
'Folgender Aufruf löscht alle Zeilen wenn in Spalte A '10' enthalten ist:
'DelRows Range("A:A"), 10

Application.ScreenUpdating = False
Application.Calculation = xlManual
If Application.WorksheetFunction.CountIf(rngSpalte, varValue) > 0 Then
If varValue = "" Then
rngSpalte.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Else
With rngSpalte
.Replace "", "##@@##", xlWhole
.Replace varValue, "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Replace "##@@##", "", xlWhole
End With
End If
End If
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Run the following line for your purposes:


Sub DelRowsTest()
DelRows Range("A1:H100"), _
Application.InputBox("Enter a value where " & _
"the rows are to be deleted ")
End Sub



Mit freundlichen Grüssen
Thomas Ramel

 

[Otto Moehrbach]

Misty
This should do what you want. HTH Otto
Sub Macro3()
Dim userResponse
Dim c As Range
Dim b As Long
With Worksheets(1).Range("A1:H100")
userResponse = InputBox("Enter a value where the rows are to be
deleted.")
For b = 1 To 100 'Number >= count of rows in the above range
Set c = Nothing
Set c = .Find(What:=userResponse, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
Else
Exit For
End If
Next b
End With
End Sub