Selecting Ranges Based on Cells with same Values

Hello,

Hope You can help me.

Am just taking first tentative steps into macros and VBA. Finding most of my info from sites like this so thanks for that.

I am struggling with something recently though and don't seem to be able to find a way to do what I want. It'll probably be really simple though...

If I have a list of values in column A which are sorted from low to high but there are different counts of each value....

eg

1
1
1
2
2
3
4
4
4
4
5
6
6



.....and I want to select ranges in column B like this ... "Range("B1:B3,B4:B5,B6,B7:B10,B11,B12:B13").Select" where each range represents a value in column A how would I do that?

The values in column A are not always the same but they are always sorted as mentioned above.

I am aiming to merge the cells in Column B based on the values in column A as per the attached Book6.xlsx

Thanks in advance

for example

Please Login or Register  to view this content.

Thanks for the quick response but I think I may not have explained very well. The values I used were only an example, I didn't think they would be specifically used for the basis of a solution.

I've attached a better example using data in the format I am actually using.

Sorry for the confusion.

Book7.xlsx

Well, if I understand correctly ... try

Thanks Nilem, this works with the data I supplied but when I've applied it to my actual data I'm getting an error on this line....

Range(Mid(adr, 2)).Select

The error I get is :-

"Run-time error '1004';

Method 'Range' of object '_Global' failed"

Would the code only work for a limited number of ranges? It seems to work with anything up to 21 ranges specified but anything more than that I get the error

Just found that a range string can only be up to 255 characters.

Is there a way of getting around this?

Nick
Can you show some of your actual data?
And if you explain why this is necessary, then perhaps we will find a more suitable solution, without selecting of cells.

I can't supply full data because it is financial information but I have attached a spreadsheet with the format I am working with.

The requirement is to merge cells in column I and J so that there is one merged cell containing the "Notes" and one containing the "Manager Comments" for each debtor reference.

Hope that helps

Book12.xlsx

try it

Please Login or Register  to view this content.

Brilliant!!

Works perfectly apart from the merge cells warning about upper left data only and an error when it completes but that's not a big deal for me.

Thanks so much for yo