Learn About Creating VBA Named Ranges in Microsoft Excel
Do you know that you can create a named range to refer a group of cells? This will help you eliminate the task of selecting a certain number of cells every now and then.
Instead of referencing a row or column, you can create a custom name.
Moreover, you can rely on a named range to specify the data stored in your cells. It is confusing to keep using combinations of letters and numbers when referencing cells.
If you are working on a large data, it is more useful to have an immediate insight of the information stored in a cell.
For instance, instead of B7, you may use Shipping Charge. Also, instead of C4, you may use Quantity as an identifier.
How Should You Use Named Ranges?
Apart from the general benefit of using named ranges given above, you can take advantage of these when playing around with tons of numerals. If your work is related to accounting, you definitely need ranges that will organize your variables.
For instance, you want to segregate items that change frequently and infrequently.
Is It Difficult to Create Named Ranges?
You should not be concerned about the difficulty of creating named ranges. Be reminded that it is very easy, which will be proven by the instruction to be given later on.
However, it is a professional tip that you should ensure the names are unique from one another, especially if you will work on tons of ranges. If you want to create names out of various words, it is best to use an underscore or capitalize every beginning of the words.
Steps in Naming a VBA Range
Given that you are done with the range number, you can create an identifier for it. Given below are steps in doing so:
Step 1. Procedure Testing
You need to run the procedure you have created. For instance, you have used the Range method to set text values for the cells you have selected. Simply click the Play button to test your syntax.
Afterward, you need to view your worksheet or workbook.
Step 2. Range Selection
Once on your worksheet, you must select the cells where the range has been configured. You will find that every cell covered by your range will have your specified text value.
In this case, the selection will be made manually. You will begin to the upper-left corner until you reach the lower-right corner of the rectangular range of cells.
Step 3. Range Name Specification
Afterward, you proceed to use the cell address box to add the range’s name. You will find the cell address box as the first text box from the left side of the worksheet.
For instance, you can name the range as NameOne. Lastly, you hit Enter to apply the changes.
Step 4. Testing
Every time you select the cell address box, there will be a combo box showing where the name of your range can be found. By selecting the range name (i.e., NameOne), you can automatically select all the cells in that range.
Step 5. Range Name Usage
You can return to the module and use your specified range name in your code.
You can simply type in Range, followed by open and close parentheses. Inside the parentheses are quotation marks. Then, you can add the range's name inside the said quotation marks. Lastly, you add an equal sign, which will be followed by a numerical or text value for the range.
Conclusively, you will have this code: Range (“NameOne”) = 75.
Step 6. Testing Range Name as Object
To find out if your configured range name is already working, you simply have to hit the Play button. Check your worksheet, and see if the values of the cells have already changed to your preferred value, which is 75.
How Do You Delete Named Ranges?
Note that even if you insert or adjust cells, the named range will adjust accordingly. However, if you will remove all the cells within a range, the reference for the named range will be lost. Expectedly, you will have the #REF error.
There is a possibility that you will have to delete the named ranges you have set. You can do so without using a code.
You can use the Name Manager in order delete a named range manually. You simply need to open the name manager. Afterward, you need to choose a range you wish to delete. Lastly, you need to click the Delete button.
If you need to delete more than one named range simultaneously, you can simply press Shift and, click the named range simultaneously. Otherwise, you can simply press Ctrl and, choose multiple named ranges. The last step will also be pressing the Delete button.
You may also delete ranges easily if there are named ranges with errors. You simply have to filter the named ranges first. There is a Filter command in the Name Manager. Click it, and select Names with Errors.
You will be provided with a list of named ranges with errors. Simply press Shift, and click all the names. Afterward, press the Delete button.
The aforementioned facts above and steps will help you manage your Microsoft Excel worksheet better. You will be able to reduce your manual tasks instead of spending a lot of time selecting cells repeatedly. The steps are useful, especially when working on sensitive and large-scale data.
