Named ranges are super cool.

 

 

Wouldn’t it be even better if we wouldn’t have to keep re-adjusting them? Imagine if they would expand and contract according to what is inside of them?

 

 

 

Guess what.

 

 

 

It is possible, with a couple of clever formulas.

 

 

 

Here I have to credit to an unknown author.
I didn’t come up with this option. I am nowhere near as clever.
Nobody knows who invented this technique in excel, but perhaps it is not the most important thing here.
The most important thing is to spread this technique to as wide of an audience as I can.

 

 

 

If you are here I presume that you understand the basics of Named ranges.
So I will not bore you with this.

 

 

 

For the coming exercise we will add the formulas for the named ranges in the Name Manager under the Formulas tab.

 

 

 

 

Expanding and contracting named ranges

 

 

All of the examples presume that the cells you trying to add to a dynamic range are in Column B.

 

 

 

For only numeric values in the cells use:

 

 

 

 

=OFFSET($B$1,0,0,COUNT($B:$B),1)

 

 

For mixed (numeric and text) values in the cells use:

 

 

 

 

=OFFSET($B$1,0,0,COUNTA($B:$B),1)

 

 

 

Numeric only

 

 

 

Expanding named ranges in excel for numeric cells.

 

 

 

Expanding named ranges in excel for numeric cells.

 

 

 

Numeric and Text

 

 

 

Expanding named ranges in excel for numeric and text cells

 

 

 

Expanding named ranges or simply named range, named ranges in excel for numeric and text cells

 Hope that you can use this technique in your day to day work.

If you like this article and want to learn excel with us, you can follow us on our facebook page. Our blog is also exists in hungarian.

 

Check out our excel VBA tips!

You can find brilliant articles in our Excel VBA Category

Delete Named Ranges with VBA

Delete named ranges with VBA code

Thomas Szigeti Another one of those issues that you come across when you are busy, and have to do things on the fly. When your manager sends you a file to adjust something. Then you open the file, and start to get the feeling that "something is not right here"...And...
How to add code to workbook

How to add an Excel VBA code to my workbook?

In this post I will explain how to add any code to yook to run macros.

Export Range to jpg with Excel VBA

A code to be able to save a range from your workbook as a .jpg picture.

Open file dialog

Open file dialog in Excel VBA

Call the windows open file dialog from excel VBA. Allow your users to pick the file your macro need.

Clear the clipboard

Excel VBA Clear the clipboard

How to clear the clipboard in VBA. Come and grab the code from us.

Delete Rows with VBA

Delete rows with VBA?
There is a trick to it! Come and grab the ready made code from us.

Remove accents in excel

Remove Accents in text with a custom VBA function

Remove accents in excel with this VBA code.
Come and grab the ready made code.

Convert conditional formatting with vba

Convert Conditional formatting

Sometimes it is required to convert conditional formatting in to cell formatting. We have written the code for you. You can copy and paste the ready made code to your project.

Square root

Square root VBA

This post will show you how to find the square root in Excel VBA.

Add button in VBA

Automatically add button to worksheet in VBA with a macro assigned to it.

This post will be for a solution that I had to develop to a database where I had to give the option to my end users to be able to delete a row with no hassle. Here I create a button, and if you press that button you can run a piece of code.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]