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
Пікірлер: 23
Thank you so much Greg for the valuable tutorial, sharing the code, especially the clear video. Thumb up 👍
You deliver great stuff man 👏💪👏
@greggowaffles
Жыл бұрын
Thanks 💪😎
This did my job. Thank you.
@greggowaffles
9 ай бұрын
No problem! Happy to hear that.
Cool, I like it !
@greggowaffles
Жыл бұрын
Thanks a lot!!
@Nick_88888
Жыл бұрын
@@greggowaffles keep them going, learning a lot.
@greggowaffles
Жыл бұрын
Will do!
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! 😴😴 kzread.info/dash/bejne/ipab0rSwo5q8crQ.html
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?
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 _
worked. but too slow. is there faster way?
Hi Greg, how do I delete rows if cell contains a Certain Time Value???