Help with Dynamic named range

djreiswig

Well-known Member
Joined
Mar 13, 2010
Messages
523
I am trying to define a dynamic named range in Excel 2000 & 2003

The format of the column I am referencing is:

Header
(blank row)
Data 1
Data 2
Data 3
ect...

The column is formatted as general and the data can be either text or numeric. The range needs to include row 2 (the blank) down to the last filled row.

I currently have the range set as =OFFSET($A$2,0,0,COUNTA($A:$A),1)

This works fine unless I start with only the header and blank row 2 and then add something to row 3. In this case the range fails to expand to include the data. It just references the blank row.

I tried =OFFSET($A$2,0,0,MAX(($A:$A<>"")*(ROW($A:$A)))-1, 1) that I found on another forum. It appears to be valid in the Name box, but when I try and reference the range in code I get an error. I also can't get it to box the cells on the sheet when I am in the name box. The first formula draws a blue box around the referenced cells.

Any help?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Maybe this (assuming A2 is always blank)
=OFFSET($A$2,0,0,COUNTA($A:$A)-1+1,1)
reduces the count by 1 to remove the header and adds 1 to include the blank cell, so the expression you posted:
=OFFSET($A$2,0,0,COUNTA($A:$A),1)
should work fine if the named range starts with the blank cell. Works ok for me.
 
Last edited:
Upvote 0
I played with a few examples.

Tester</SPAN>
=OFFSET(Sheet1!$A$2,0,0,MATCH(REPT("Z",255),Sheet1!$A:$A,1)-1,1)</SPAN>
Tester2</SPAN>
=OFFSET(Sheet1!$A$2,0,0,MAX((Sheet1!$A:$A<>"")*(ROW(Sheet1!$A:$A)))-1, 1)</SPAN>
Tester3</SPAN>
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)+COUNTBLANK(Sheet1!$A:$A),1)</SPAN>
Tester4</SPAN>
=OFFSET(Sheet1!$A$2,0,0,MAX(MATCH(REPT("Z",255),Sheet1!$A:$A,1),MATCH(9.9999E+207,Sheet1!$A:$A,1))-1,1)</SPAN>

<TBODY>
</TBODY>

Tester fails on mixed data because it finds the last text value and ignores numbers below it
Tester2 (your example) works for me
Tester3 fails. The COUNTBLANKS fills the column, and although the formula was accepted I got a formula error using INDEX.
Tester4 works but it's a mouthful. Gets the greater of (last text location, last number location) and the range is correct. Also doesn't throw an error when referenced.

Does any of that help? And how are you referencing the name in code?

Denis
 
Upvote 0
Thanks for the reply.

I think I may have figured out what happened. It appears that if the header is blank then the range doesn't expand to include the new row. So if I add something to row 3 the range only includes row 2. Not sure why, but that is how it works. I am adding the new row in code, so I failed to add the headers if the sheet starts out blank.
 
Upvote 0
Thanks for the reply.

I think I may have figured out what happened. It appears that if the header is blank then the range doesn't expand to include the new row. So if I add something to row 3 the range only includes row 2. Not sure why, but that is how it works. I am adding the new row in code, so I failed to add the headers if the sheet starts out blank.
If the header is blank and you add something to row 3, counta is = 1 and your offset formula returns one cell starting at A2 as it should.
 
Upvote 0
Thanks everybody. The range works as I had it, I just didn't have the header text where it should be.

JoeMo: I guess that comes from too much gathering code and making it work without fully understanding how the functions all work. I understand now that it is not counting my header as a row if it is blank so the range doesn't include the expected number of rows. Completely self taught VBA (thanks to guys like you, and everybody else that contributes to the board). Learning something new every day.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top