For Each Range in Range???

[Captain_Conman]

Hi Everyone,

Instead of using "For each Cell in Range" I was wondering if there is a way of doing "For each Range in Range"

For example, in column A, I have a list of store numbers. It may appear one time, or it may appear multiples...

[TABLE="width: 133"]
<colgroup><col></colgroup><tbody>[TR]
[TD]STORE 1358[/TD]
[/TR]
[TR]
[TD]STORE 1358[/TD]
[/TR]
[TR]
[TD]STORE 1362[/TD]
[/TR]
[TR]
[TD]STORE 1389[/TD]
[/TR]
[TR]
[TD]STORE 1389[/TD]
[/TR]
[TR]
[TD]STORE 1411[/TD]
[/TR]
[TR]
[TD]STORE 1419[/TD]
[/TR]
[TR]
[TD]STORE 1419[/TD]
[/TR]
[TR]
[TD]STORE 1426[/TD]
[/TR]
[TR]
[TD]STORE 1427[/TD]
[/TR]
[TR]
[TD]STORE 1429[/TD]
[/TR]
[TR]
[TD]STORE 1433[/TD]
[/TR]
[TR]
[TD]STORE 1433[/TD]
[/TR]
[TR]
[TD]STORE 1434[/TD]
[/TR]
[TR]
[TD]STORE 1434[/TD]
[/TR]
[TR]
[TD]STORE 1435[/TD]
[/TR]
[TR]
[TD]STORE 1435[/TD]
[/TR]
[TR]
[TD]STORE 1446[/TD]
[/TR]
[TR]
[TD]STORE 1485[/TD]
[/TR]
[TR]
[TD]STORE 1509[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
[TR]
[TD]STORE 1513[/TD]
[/TR]
</tbody>[/TABLE]

Essentially, I would like to loop through column A, but instead of looking at each cell, is there a way to look at each group of cells (each store)? What code would look at the group of cells instead of just one at a time?

 

[Joe4]

Unless you have predefined named ranges ahead of time, I don't think there is any way to do that. There is nothing "inherent" that tells Excel that cells with the same value are blocked off into distinct ranges. You would need to define it ahead of time. And if this is a dynamic situation, that really is not going to be feasible.

 

[Captain_Conman]

I figured that may be the case, but I have thousands of store numbers... I will have to figure something else out! Thanks

 

[Joe4]

What exactly is it that you are trying to do?
If the issue is the code is slow because you are looping through each cell, there are ways to speed it up.
If you post your code and explain what you are trying to do, we may be able to help you with that.

 

[Fluff]

Depending on what you are trying to do, one possibility (If your stores are sorted) maybe to insert a blank row between different stores & you could then look at areas

 

[Captain_Conman]

I apologize if this is turning into a duplicate thread. But please refer to this post to get a better understanding of my goal... https://www.mrexcel.com/forum/excel-questions/1065716-highlight-rows-based-sum-multiple-cells.html

Essentially, I exported a bunch of data from our ERP system...

If the store numbers are the same, the PO#s are the same, and the sum of the balances equal zero, I want the rows to be highlighted.

So for each store, I wanted to find out if any number of the transactions can balance out to zero, if summed.

However, the number of transaction may vary, and a "match" may include more than one line.

I know this is complex and I am not explaining it well, but I really do appreciate your help.