Selecting ranges & more

[twollens]

Ok, I have something that I am trying to do in VBA, and am completely a
a loss (even how to do it normally)

I need to take a spreadsheet that looks like this:

1 USER1 ASIA
2 USER2 ASIA
3 USER3 AMERICA
4 USER4 AMERICA
5 USER5 EUROPE
6 USER6 EUROPE
7 USER7 EUROPE

and end up with the following

USER1, USER2
USER3, USER4
USER5, USER6, USER7

Basically I need to comma seperate all the users from a region. Th
number of users in each region will not be the same. There is a chanc
that a region might not even be in the spreadsheet. There will be n
more than those 3 regions. They are already sorted in alphabetica
order by region.

Any ideas?

Thank

 

[Tom Ogilvy]

Sub Tester7()
Dim sStr(1 To 3) As String
Dim rng As Range
With ActiveSheet
Set rng = .Range(.Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
End With
i = 1
For Each cell In rng.Offset(0, 1)
sStr(i) = sStr(i) & cell.Offset(0, -1).Value & ", "
If cell.Value <> cell.Offset(1, 0).Value Then
i = i + 1
End If
Next
For i = 1 To 3
sStr(i) = Left(sStr(i), Len(sStr(i)) - 2)
Cells(i, 5).Value = sStr(i)
Next

End Sub

 

[Guest]

twollens

This should work for you. Let me know

Public Sub SeparateByRegion(

' Optional Declaration
' Don't really need these, can just be normal strings that are appended to
' Personally, I like arrays because they offer more flexibility if you want t
' change something later
Dim sAsia() As Strin
Dim sAmerica() As Strin
Dim sEurope() As Strin

Dim iAsiaRunner As Intege
Dim iAmericaRunner As Intege
Dim iEuropeRunner As Intege

' Don't forget setting these to -1, otherwise, your arrays will have an extra space
iAsiaRunner = -
iAmericaRunner = -
iEuropeRunner = -

' Required Declaration
Dim i As Intege
Dim iRows As Intege

iRows = ActiveSheet.UsedRange.Rows.Coun

For i = 1 To iRow
Select Case Range("C" & i
Case "ASIA
iAsiaRunner = iAsiaRunner +
ReDim Preserve sAsia(iAsiaRunner
sAsia(iAsiaRunner) = Range("B" & i
Case "AMERICA
iAmericaRunner = iAmericaRunner +
ReDim Preserve sAmerica(iAmericaRunner
sAmerica(iAmericaRunner) = Range("B" & i
Case "EUROPE
iEuropeRunner = iEuropeRunner +
ReDim Preserve sEurope(iEuropeRunner
sEurope(iEuropeRunner) = Range("B" & i
End Selec
Nex

Range("A" & iRows + 2) = "ASIA
Range("B" & iRows + 2) = Join(sAsia, ", "
Range("A" & iRows + 3) = "AMERICA
Range("B" & iRows + 3) = Join(sAmerica, ", "
Range("A" & iRows + 4) = "EUROPE
Range("B" & iRows + 4) = Join(sEurope, ", "

End Su


----- twollens > wrote: ----

Ok, I have something that I am trying to do in VBA, and am completely a
a loss (even how to do it normally

I need to take a spreadsheet that looks like this

1 USER1 ASI
2 USER2 ASI
3 USER3 AMERIC
4 USER4 AMERIC
5 USER5 EUROP
6 USER6 EUROP
7 USER7 EUROP

and end up with the followin

USER1, USER
USER3, USER
USER5, USER6, USER7

Basically I need to comma seperate all the users from a region. Th
number of users in each region will not be the same. There is a chanc
that a region might not even be in the spreadsheet. There will be n
more than those 3 regions. They are already sorted in alphabetica
order by region

Any ideas

Thank