It’s #MacroMonday again This blog post will show you how to store the current range selection with VBA in Excel. This is a very useful way to select all the cells that are contained within a Range that you might want to work with later in your code. We will also show you an example of how to use the stored range. I do get this question quite regular, so thought it would made a great blog post to share. So let’s get started!.
The VBA Range Selection In Excel A Working Example.
This is a common enough piece of code used in Excel VBA where you want to store the range of cells that are selected within the worksheet by the user. Susequently, this range is usually used later in the code. It is usually easier to work through an example. In this case I will change the colour of the selected range to RED.
Let’s get coding then!
Step 1. Starting The VBA Code To Turn The Range Of Cells RED.
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.
When you decide to write an Excel macro, you need to choose where to store it. You have two choices.
- Store it in the current Excel workbook you are working on creating.
- Save the macro in your Personal Macro workbook.
As I already know that this macro is a handy one I will store in in my Personal Macro workbook. This macro can be re used to turn any user selected range of cells red. So it is a keeper.
Step 2. Name Your Macro.
The first step in writing the VBA macro to store our range of cells to turn red is to name the macro. Stert by typing Sub then the name of the macro. In this example I keep it simple with StoreRange(). Notice once you hit enter on the name of the macro Excel automatically inserts the End Sub line of code. Any other lines of VBA code should be written between these two lines.
Step 3. Declare Variables.
We need to Declare 2x Variables. This just means that we allocate some memory in Excel for them. We are declaring the active worksheet and the range of cells.
Step 4. Store The Active Worksheet And The Current Selection Of cells.
The next two lines of code set the active worksheet and the user selected range of cells. This is the selected range which will turn red when the next line of code executes.
Step 5. Turn The VBA Range Of Cells RED.
We can now use any piece of code, to perform the action we want. However, in this example we have instructed Excel to turn the current range of selected cells the colour RED.
Step 6. End The Macro.
Once the user selected range of cells turns red, the macro ends. the End Sub code is already in the code window from the time of naming the macro. That’s it. Really straightforward.
Want To Copy The Code?
Sub StoreRange()
'macro by how to excel at excel
Dim ActSheet As Worksheet
Dim MyRange As Range
Set ActSheet = ActiveSheet
Set MyRange = Selection
MyRange.Cells.Interior.ColorIndex = 3
End Sub
With this article, we’ve covered the basics of how to store a range selection in Excel with VBA. We hope that you found it helpful and interesting! If you want more information on what else is possible with VBA programming for Microsoft Office products like Excel, please feel free to subscribe to the How To Excel At Excel Newsletter, YouTube channel or personally recommended courses.
Even More Excel Tips.
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.