Excel VBA Macro: Delete Rows if Cell Does Not Contain a Certain Text (String) Value

Excel VBA Macro: Delete Rows if Cell Does Not Contain a Certain Text (String) Value
💥Subscribe: / @greggowaffles
Code:
Sub delete_all_rows_without_string()
Dim row_count As Long
Dim i As Long
Dim ws As Worksheet
Dim my_string As String
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Activate
row_count = ws.Cells(Rows.Count, "A").End(xlUp).Row
i = 1
Do While i ＜= row_count
my_string = ws.Cells(i, 1).Text
If InStr(my_string, "hostname") = 0 _
And InStr(my_string, "transport output none") = 0 Then
Rows(i).EntireRow.Delete
i = i - 1
row_count = row_count - 1
End If
i = i + 1
Loop
End Sub
#excelmacro #excelvba

[@Jojosmith342]

Thank you so much Greg for the valuable tutorial, sharing the code, especially the clear video. Thumb up 👍

[@alexrosen8762]

You deliver great stuff man 👏💪👏

[@greggowaffles]

Thanks 💪😎

[@subhash9chack]

This did my job. Thank you.

[@greggowaffles]

No problem! Happy to hear that.

[@Nick_88888]

Cool, I like it !

[@greggowaffles]

Thanks a lot!!

[@Nick_88888]

@@greggowaffles keep them going, learning a lot.

[@greggowaffles]

Will do!

[@odijin]

Holy moly!!! This is awesome! Thanks very much for sharing your excel skills with this video. I learned a bunch. This video is particular useful for me because it shows how to loop through certain values in cells and than calls for an action. I have a slightly different scenario and wanted to ask your help. I have an excel file with multiple sheets like 100+ sheets, and I want to look for a value in each of those sheets and once that value is identified, is there a way to delete that sheet or sheets altogether? For example, if I used your codes in this video to loop through all the sheets searching for a specified value/string in a call in each of the sheets, then how can I delete all the sheets that contain that value? Thanks for the knowledge!!

[@greggowaffles]

Thanks! Would this value be found in any column on any sheet, or are we looking at the same column across all sheets?

[@odijin]

@@greggowaffles Thank you for the response. To answer your question, this value would be found in the same column and row in all the sheets. So for example in every sheet if cell A1 has the string "Turtle Soup" then that entire sheet should be deleted, and it loops through all the sheets looking for turtle soup in cell A1 and deletes any sheets containing that value. Hope my explanation helps. And again, thank you so much for your guidance!!!!!

[@greggowaffles]

@@odijin thanks for clarifying! I’ll drop a video on that tomorrow!

[@odijin]

@@greggowaffles looking forward to your video! much appreciations.

[@greggowaffles]

@@odijin done! 😴😴 kzbin.info/www/bejne/jpPKoYWtqJyJeLM

[@raeaustin]

Is it possible to delete everything that doesn't match cell B1? In the example, hostname would be in B1. If InStr(my_string, "if it matches B1") = 0 _

[@judahlee7837]

Hi Greg, how do I delete rows if cell contains a Certain Time Value???

[@nullhas]

Fantastic Greg. How we can use this to delete rows (if a column contains duplicate email id or mobile number)?

[@greggowaffles]

Thanks! Do you have a specific example?

[@nullhas]

@@greggowaffles Yes…let me know how can I share?

[@imronnesia5170]

worked. but too slow. is there faster way?