How to find the addresses?
 

How do I find the addresses for the ranges of B column to D column for
each group in column A using VBA? For abc, I want to find B1:D3; for
efg, find B4:D9 and etc.

A B C D
1 abc 35000 23920 30780
2 abc 36975 23620 26799
3 abc 31800 28315 21528
4 efg 33863 24128 23400
5 efg 38213 23650 18720
6 efg 24000 41278 30600
7 efg 30000 32000 24794
8 efg 27583 26520 27405
9 efg 34000 33140 36627
10 xyz 30900 27192 29781
11 xyz 27600 25000 41000
12 opq 28912 32500 20801
13 opq 21936 20500 40174
14 opq 38000 16641 21840
15 opq 26499 24500 24516
....
....

I appreciate your help. Thanks.

Faye Larson

How to find the addresses?
 

Sub ShowRanges()
Dim rStart as Range, i as Long
Dim grp as String, rng as Range
set rStart = Range("A1")
grp = rStart.Value
i = 2
do while cells(i-1,1) < ""
if cells(i,1) < grp then
set rng = range(rStart,cells(i-1,4))
msgbox grp & " address is " & rng.Address(0,0)
set rStart = cells(i,1)
grp = rStart.Value
end if
i = i + 1
Loop
End Sub

Might be a start.

--
Regards,
Tom Ogilvy

"Faye" wrote:

How do I find the addresses for the ranges of B column to D column for
each group in column A using VBA? For abc, I want to find B1:D3; for
efg, find B4:D9 and etc.

A B C D
1 abc 35000 23920 30780
2 abc 36975 23620 26799
3 abc 31800 28315 21528
4 efg 33863 24128 23400
5 efg 38213 23650 18720
6 efg 24000 41278 30600
7 efg 30000 32000 24794
8 efg 27583 26520 27405
9 efg 34000 33140 36627
10 xyz 30900 27192 29781
11 xyz 27600 25000 41000
12 opq 28912 32500 20801
13 opq 21936 20500 40174
14 opq 38000 16641 21840
15 opq 26499 24500 24516
....
....

I appreciate your help. Thanks.

Faye Larson