Visual Basic for Applications Macro to Delete Rows of Cells in Microsoft Excel Based on a Criterion

This code will loop through a selected area of a column and delete rows for you. The macro will loop through the selected part of any column searching for whatever you specify. When the macro finds a cell whose content meets your criterion, it will delete the cells corresponding row and then proceed onward: it will continue looping through the remaining cells of the selected column and if it finds another cell whose content matches your criterion - it will delete that cells corresponding row. It will do this until it has looped through every cell of your selection.



To use the code you have to open Microsoft Excel's Visual Basic programming interface. This is done via the "Developer's" tab in the "Ribbon" of Excel. The pic I've uploaded here shows Excel's Ribbon and the Developer's tab is selected. The Developer's tab is not present by default. If your Developer's tab isn't present, click on the "Office Button," this is the button with the Microsoft Office logo in the upper-most corner of all Office applications. Click that button, and then click on "Excel Options" in the lower-right corner of the dialog box that appears. You should be in the "Popular" pane of the Excel Options dialog box by default. I've included a picture of that dialog box in this post. You can clearly see what I'm referring to for reference. The third selectable option down is "Show Developer tab in the Ribbon." Select that, and click "OK."



Click your Developer tab. To the far right, on the Ribbon, you'll see "Visual Basic," click that. You'll then enter the Microsoft Visual Basic programming platform. Click "Insert" and choose "Module." Copy and paste the macro below into the "Module" that appeared as a result of your clicking Insert, then Module.

Close out of the Module by clicking the red in the upper-most right corner of its dialog box. Also exit the Visual Basic platform by clicking on its red x, upper-right corner.

Now you're ready to run your macro. Select the target region of the column containing the data you want to auto-remove, click on "Macros" (the button immediately to the right of the "Visual Basic" button), highlight the macro titled "Find_and_delete," then click "Run."



A dialog box will appear asking you for input. This is where you'll enter the information/data (for example, cat) you want to remove all instances of. Click "OK"

You're done.

Please remember to save your work prior to running any macros and do it under a different name. This is for safety. It's to make sure accidental data loss from running a macro doesn't become permanent data loss. You cannot just click "undo" and retrieve the data you'd have lost from running a macro: automated changes cannot be reversed in the way manual changes can.

-----------------------------------------------------------------------------


Sub Find_and_delete()
Dim rng As Range
Dim what As String
Dim strMsg As String
Dim lok4 As String
Dim count As Integer
count = 0
lok4 = Application.InputBox("What variable?")
Do
Set rng = ActiveSheet.UsedRange.Find(lok4)
If rng Is Nothing Then
Exit Do
Else: Rows(rng.Row).Delete
count = (count + 1)

End If
Loop
MsgBox "The number of deleted cells was " & count


End Sub