delete every row where value equals user input
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 -- Message posted via http://www.officekb.com |
delete every row where value equals user input
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 |
delete every row where value equals user input
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 "Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote in message news:79e27dfb52b63@uwe... 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 -- Message posted via http://www.officekb.com |
All times are GMT +1. The time now is 01:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com