Hello Excellers an welcome back to another #MacroMonday #Excel tip in my 2020 series.  Today let’s take alook at how to select the current region on an Excel worksheet with a simple Excel Macro.  So, what is the Current Region on an Excel worksheet?.

What Is The Current Region?

The current region is a range of cells bounded by and combination of blank rows or blank columns.  Let’s take a look at some examples to explain, and see how this is really useful when we are writing an Excel VBA Macro.

So, what is the current region of cel A1 below?.

 

Well, let’s take a look at how to Excel selected current regions. We can do this by using a command button on our Excel worksheet, which we can click to select the current region.

Adding An Excel Macro Command Button.

First, we need to select the Developer Tab in Excel. If you do not see the Developer Tab, you need to enable it. This is very straightforward. Read my blog post below for instructions on how to enable the Developer Tab.

How To Enable The Developer Tab In Excel

• On the Developer Tab, Click Insert.
• In the ActiveX Controls group, click command Button.
• Drag the button to your worksheet.

Adding Excel Macro Code To The Button.

Now, we have the command button on our Excel worksheet we can add a line of simple code to highlight the current region.

• Right-click CommandButton1 (make sure Design Mode is selected).
• Click View Code and the Visual Basic Editor appears.
• Place your cursor between Private Sub CommandButton1_Click() and End Sub.
• Enter your code. In this example, it is REALLY simple.

Range(“A1”).CurrentRegion.Select

So, now when we click the button, the current region is selected. This is the equivalent of using the keyboard shortcut of Ctrl+A.

So, how about the current region of the cells below? We change the code to

Range(“B3”).CurrentRegion.Select

The selected region is now A3 to C4,  as row 1 has no influence on the region of  A3 to C4 as row 2 is empty.

So, that is how to select the current region with some simple Excel Macro VBA code.  This is the same a using the Ctrl+A once with your keyboard.

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

Macro Mondays Blog Posts.

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.  All you need to do is choose the Excel task that most closely describes what you need to be done.  Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements.  Try It!.  Need Help With An Excel VBA Macro?.  Of course, you don’t need to ask how to list all files in a directory as it is right here for free.