Hello Excellers, and welcome back. It is time for another instalment in my #MacroMonday, #Excel blog posts in my Excel series. This Macro demonstrates how to use a VBA input box to determine which Excel rows to delete. However, instead of having the parameters that determine which rows to delete hard-coded into your Macro, I use 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.
Macro Mondays – Excel VBA InputBox Function – The Basics
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. These IDs are no longer customers. Therefore, the information is no longer required..
What Does The Macro Do?
This Macro will delete a whole row or rows in Excel, determined by the user’s information, into an input box. In this example, the user will enter the customer ID stored in Column A of our sample data set.
Starting The VBA Macro.
The Macro uses the Input Box method. Using a VBA input box takes the VBA macro to the next level. In the input box, we can interact with our users as we are getting the user to specify the Customer ID that will identify the rows of data 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?.
If you save the Macro in your Personal Macro workbook, it will be available in your 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 VBA code repeatedly so that in this instance, I store it in my Personal Macro Workbook. When you select this option, Excel creates (if it does not already exist) and saves the Macro in that location. By default, this macro workbook is named Personal.xlsb. This Macro is a particular procedure used only in this Excel workbook. So I am storing the Macro in the current workbook I am using this time.
Preparing To Write The Macro.
We need to start off the process by inserting a New Module, then Insert Module. Type Sub then the name of your macro. In this example, I have called the macro DeleteMyExcelRows. Notice that Excel will aWe need to start the process by inserting a New 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. Next, we need to enter the rest of the code to run the Macro between these two lines of code.
Sub DeleteMyExcelRows
End Sub
Declaring Variables.
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).
Dim IDSelect As String
Dim c As Range
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.
Let IDSelect = InputBox("Please Enter The Customer ID you want to delete")
If IDSelect = vbNullString Then Exit Sub
Using The For Next Loop With The VBA 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.
For Each c In Range("A:A")
If c = IDSelect Then c.EntireRow.Delete
Next
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.
End Sub
Let’s Test The Macro!
Want The Excel VBA Code To Use The Input Box?
Sub DeleteMyExcelRows()
'Macro by How to Excel At Excel
Dim IDSelect As String
Dim c As Range
Let IDSelect = InputBox("Please Enter The Customer ID you want to delete")
If IDSelect = vbNullString Then Exit Sub
For Each c In Range("A:A")
If c = IDSelect Then c.EntireRow.Delete
Next
End Sub
If you are looking for a way to quickly delete specific rows in Excel, an input box can be a useful tool. In this blog post, we showed you how to create an input box that will allow you to select the rows you want to delete. We also provided instructions on how to format the input box so that it is easy to use.assist you further.
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
How To Excel At Excel – Macro Mondays Blog Posts.
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips on the first Wednesday of the month.
