For each cell in range loop does not recognize expanding range

**jhren** · 07-06-2013, 06:31 PM

Help!!!

Have the following code:

Please Login or Register  to view this content.

Essentially it deletes all but first (top) cell in a named range (panel_sort) and rebuilds it (just in case there are any changes in source data). It works fine for the first set of loops to lookup the first cell in the source data and return vlaues indexed one column to left and put those values in the named range. The next step is to do a lookup of the second cell in the named range... but the For Each loop doesn't recognize that the named range has grown since the first loop.

Being a VBA noob, I don't know how to fix it....

**jhren** · 07-06-2013, 06:42 PM

Please Login or Register  to view this content.

Okay, just realized "Delete" should be "ClearContents".

But that's not a fix for the topic problem...

**jhren** · 07-06-2013, 08:39 PM

I think I solved it myself...

Please Login or Register  to view this content.

Any of you VBA gurus see any mistakes, or better method...???

**MarvinP** · 07-06-2013, 09:41 PM

Hi jhren,

Why don't you use a Dynamic Named Range on your sheet to give you the number of rows.

see http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

**jhren** · 07-07-2013, 07:49 AM

Range "panel_sort" is a dynamic named range. That's part of the original problem. The first part of the code drops "panel_sort" to a one cell range. That's all the For Each loop sees, even though "panel_sort" grows during the loop. With a Do While loop, the growth registers on successive passes.

Didn't try a For Each loop based on "panel_sort" row count, because the Do While loop I tried worked.