Hello, Excellers and welcome back. It is time for another installment in my #MacroMonday #Excel blog posts in my Excel 2o20 series. This macro, using an input box, 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. However, instead of having the parameters that determine which rows to delete ‘hard-coded’ into your Macro, I will be using an input box that 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.
For some basic information on using Input boxes in your Excel worksheet please read my blog posts below.
Sample Data Set And Example Using An Input Box.
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 data set. They are no longer customers and we do not need their information anymore.
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 data set.
Starting The VBA Macro.
The Macro uses the Input Box method. This takes our VBA 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.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What’s the difference?.
Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very specific procedure, so this time I am storing the macro in the current workbook I am using.
Preparing To Write The Macro.
We need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called the macro DeleteMyExcelRows. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines of code.
We need to declare two variables for this macro. This simply ensures that Excel creates memory containers for these values. In this example, we need to declare the following:-
The first variable – Dim IDSelect As String – (this will be the data that the user enters into the VBA input box).
Secondly, we use this variable – Dim c As Range – (this refers to the range of cells that contains that Excel will use to decide on which rows to delete).
How To Use The Input Box.
This is where we use the Input Box to collect the Customer ID from the user. You can use the InputBox Function in Excel VBA to prompt the user to input a value. Excel will display a very simple dialog box that asks the user for some input. The function returns whatever the user enters into the dialog box. It makes a great alternative to developing a custom dialog box if you only want to collect one value from the user to use in your code. In this example, I have asked the user to enter the ID of the customer they want to delete. If no string is entered then the code is exited at this point.
Using The For Next Loop With The Input Box.
The next few lines of code use the For Next Loop method to move sequentially through the list of Customer ID’s in Column A. Excel will delete them if they contain the same ID that the user entered into the Input Box. Each line of the range will be looped to see if the string entered by the user is a match.
Ending The Macro.
Finally, the code ends once all looping of cells has been completed with the “End Sub" piece of code. This was already entered into the module when the name of the macro was set.
Let’s Test The Macro!
Want The Excel VBA Code To Use The Input Box?
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips on the first Wednesday of the month.