In Excel, How do I Delete any row where a particular string occurs? 1

[SebAguilera]

In Excel, how do I Delete any row where a particular string occurs within a cell in the specified column. ? I assume I need a Macro of some sort but don't have the code.

Thanks Guys.

 

[Zathras]

If this is just a one-time thing, the easiest way is to:

1. put an automatic data filter on the column,
2. select only the rows containing the string, and
3. then delete the rows.
4. Finally, remove the automatic data filter.

 

[littlek]

Try this code.

Sub DelRowIfStringFound()
Dim i As Integer
Dim sStringSearch As String
Dim iNumbofRows As Integer

sStringSearch = "kk"
iNumbofRows = 100
Sheets("sheet4&quot.Select
Range("A1&quot.Select

For i = 1 To iNumbofRows
If ActiveCell.Value = sStringSearch Then 'Case sensitive.
' If UCase(ActiveCell.Value) = UCase(sStringSearch) Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

Hope that will help.

 

[Recce]

Hi Littlek

Enjoyed your code. Made 1 or 2 changes for myself and it works well.:->

Regards

 

[SebAguilera]

Thanks littlek ,
I tried it and it works great, except for one thing. I am creating these Excel files using an Access macro. The problem is tha Access apparently names the worksheet the same as the query that it came from. I.E. It changes the worksheet name of the Excel file from the default "Sheet1" to whatever the name of the query happens to be. The name of the query is never the same twice so this presents another problem. Is there a way to access the single worksheet that is created by using a wildcard or something? Or if not, how can you write code to change the name of the worksheet back to "Sheet1" so that I can hardcode Sheet1 into your macro.

Thanks again! Great Info Guys!.
Seb

 

[littlek]

AdmiralSeb,
I assume that the sheet that contains the data is the active sheet, then make small modification to the code below

Sub DelRowIfStringFound()
Dim i As Integer
Dim sStringSearch As String
Dim iNumbofRows As Integer
Dim sActiveSheet as string

sStringSearch = "kk"
iNumbofRows = 100
sActiveSheet = ActiveSheet.Name
Sheets(sActiveSheet).Select
Range("A1&quot.Select

For i = 1 To iNumbofRows
If ActiveCell.Value = sStringSearch Then 'Case sensitive.
' If UCase(ActiveCell.Value) = UCase(sStringSearch) Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

Sorry, can't make the font in different size or colour to highlight the changes. Copy and replace these with what you had before.

I am glad I could help.

Good luck.

Littlek.

 

[SebAguilera]

Thanks Littletek,

Works like a charm!!!!!

AdmiralSeb