This really is a time saver and once used, it is never tucked far away from me in my Excel and Macro toolbox. I am going to show you how to delete rows in your Excel worksheet, but instead of having the parameters that determine which rows to delete ‘hard-coded’ into your Macro, the use of an input box allows the user to specify which rows to delete. This simple piece of code gives you much more flexibility to quickly remove unwanted data from your Excel worksheet.
Check out my blog Post Macro Mondays – Excel VBA InputBox Function – The Basics for some basic information on using Input boxes in your Excel worksheet.
In my example below, let’s use an input box to collect the Customer ID’s in column A that we want to delete from our dataset as they are no longer customers and we do not need their information anymore.
If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Want To Learn How Write Excel Macros? Check Out Chandoo’s VBA Classes.
What Does The Macro Do?
This Macro will delete a whole row or rows in Excel, determined by the information the user enters into an input box. In this example, the user will enter the customer ID which is stored in Column A of our sample dataset.
How Does The Macro Work?
The Macro uses the Input Box method. This takes our macro to the next level as we can interact with our users as we are getting the user to specify the Customer ID that will identify the rows of data that they want to delete from the Excel worksheet.
The For Next Loop method is used to move sequentially through the list of Customer ID’s in Column A, then delete them if they contain the same ID that the user entered into the Input Box.
Step 1. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click on personal.xlb in the Project Window. I have stored this macro in my Personal Macro Workbook, so it is available to use whenever I start Excel and can apply it to an Excel workbook I choose to. You can also store it in the current workbook by creating a new module there as an alternative location.
Step 2. We need to declare two variables in the macro. this just means that Excel will allocate a memory container for these 2 values.
Dim IDSelect As String (the refers to the Customer ID the user will enter into the Input Box).
Dim c As Range (the range of cells that Excel will lookup the Customer ID in to see if there is a match).
Step 3. This is where we use the Input Box to collect the Customer ID from the user.
Step 4. This step instructs Excel to exit the macro at this stage if there is nothing entered into the Input Box.
Step 5. Excel then begins to loop through all of the rows in Column A to see if the value entered into the Input box is equal to the value of any of the rows.
Step 6. If a match is found then the whole row is deleted.
Step 7. When all of the rows have been looped through the routine ends.
Step 8. Test your Macro!!