Spy Journal 3.0 - WEEKNUMhttps://www.spyjournal.biz/taxonomy/term/122/all
enDynamic Ranges - and using VBA to create themhttps://www.spyjournal.biz/excel/Dynamic_Ranges_and_using_VBA_to_create_them
<p>Andrew recently posted a piece of <a href="http://blog.livedoor.jp/andrewe/archives/50353713.html">VBA code used to create dynamic ranges</a>. Very useful.<br />
Here is my <a href="/exceltips/2004/10/dynamic-range-names.html">original post on dynamic ranges</a> posted back in 2004 repeated.<br />
If you have used a range name before then you will know that it can be frustrating updating the reference of the range name if you want to add data to the range.<br />
Here are some ways you can dynamically update the range by using the OFFSET function in the range name reference.<br />
Assume for all these examples that column A has a mixture of text and numbers for several cells.<br />
Click Insert - Name - Define on the menu.<br />
In the Names in Workbook Dialog box type a range name (Eg test_range) and then try these different options.<br />
<strong>1: Expand Down as Many Rows as There are Numeric Entries.</strong><br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,COUNT($A:$A),1)</strong><br />
<strong>2: Expand Down as Many Rows as There are Numeric and Text Entries.</strong><br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,COUNTA($A:$A),1)</strong><br />
<strong>3: Expand Down to The Last Numeric Entry</strong><br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A))</strong><br />
If you expect a number larger than <strong>1E+306</strong> (a one with 306 zeros) then change this to a larger number.<br />
<strong>4: Expand Down to The Last Text Entry</strong><br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,MATCH(&quot;*&quot;,$A:$A,-1))</strong><br />
<strong>5: Expand Down Based on Another Cell Value</strong><br />
Put the number 10 in cell B1 first then:<br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,$B$1,1)</strong><br />
Now change the number in cell <strong>B1</strong> and the range will change accordingly.<br />
<strong>6: Expand Down One Row Each Month</strong><br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,MONTH(TODAY()),1)</strong><br />
<strong>7: Expand Down One Row Each Week</strong><br />
In the <strong><em>Refers to</em></strong> box type: <strong>=OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)</strong><br />
Requires the &quot;Analysis Toolpak&quot; to be installed. <strong>Tools&gt;Add-ins-Analysis Toolpak</strong><br />
The good thing about number <strong>3,4,5,6</strong> and <strong>7</strong> is that they will include blank cells.<br />
You can also change the <strong>Columns</strong> the dynamic range will span by simply changing the last <strong>Argument</strong> of the <strong>OFFSET</strong> function to a higher number than 1.<br />
You could even expand across your <strong>Columns</strong> dynamically by placing another <strong>COUNT</strong> or <strong>COUNTA</strong> formula as the last argument, instead of 1. See below:<br />
In the <strong>Refers to</strong> box type: <strong>=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))</strong><br />
This dynamic range will now also expand across <strong>Columns</strong> in <strong>Row 1</strong>. So if you add another <strong>Column</strong> to your <strong>Table</strong> the dynamic range will automatically incorporate it.<br />
Adapted from <a href="http://www.ozgrid.com/">OzGrid</a></p>
https://www.spyjournal.biz/excel/Dynamic_Ranges_and_using_VBA_to_create_them#commentsAddinCOUNTCOUNTAExcelfunctionsMATCHMONTHOFFSETTODAYVBAWEEKNUMhttps://www.spyjournal.biz/crss/node/149Tue, 30 Oct 2007 05:35:24 +0000jethro149 at https://www.spyjournal.biz