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...

STORE 1358

STORE 1358

STORE 1362

STORE 1389

STORE 1389

STORE 1411

STORE 1419

STORE 1419

STORE 1426

STORE 1427

STORE 1429

STORE 1433

STORE 1433

STORE 1434

STORE 1434

STORE 1435

STORE 1435

STORE 1446

STORE 1485

STORE 1509

STORE 1513

STORE 1513

STORE 1513

STORE 1513

STORE 1513

STORE 1513

STORE 1513

STORE 1513

<colgroup><col></colgroup><tbody>
</tbody>

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.