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

--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps