Dynamic range with gaps

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,569

Office Version

1. 365

Platform

1. Windows

• Sep 14, 2010

• #2

Here's an example where a dynamic named range is created using the INDEX, MATCH, and ROWS functions...

http://xl-central.com/create-dynamic-named-range.html

 

Upvote 0

R

roscoe

Well-known Member

Joined

Jun 4, 2002

Messages

1,046

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Sep 14, 2010

• #3

I have no clue how that works, but neither sample have gaps so without understanding it I can't modify it.

 

Upvote 0

sulakvea

Well-known Member

Joined

Jul 2, 2008

Messages

994

• Sep 14, 2010

• #4

hi roscoe, gaps or not - this should work (#3)

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,569

Office Version

1. 365

Platform

1. Windows

• Sep 14, 2010

• #5

roscoe said:

I have no clue how that works, but neither sample have gaps so without understanding it I can't modify it.

Click to expand...

In that example (the first one), the starting reference for the dynamic range is B2. Then, the last numerical value in Column B is found (gaps/blank rows are ignored), and it's reference is used as the ending reference for the dynamic range.

Also, note that it avoids the use of the volatile function OFFSET, which can prolong re-calculation times.

 

Upvote 0

R

roscoe

Well-known Member

Joined

Jun 4, 2002

Messages

1,046

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Sep 14, 2010

• #6

That #3 looks like it should work. Thanks.

I suspect the other would as well but...

 

Upvote 0