Expanding address information

[philipd]

Hi,

I am looking for an automatic solution to a problem I am having where i ahve several columns with address range information in, and I need to expand each row into the individual address components.

For example..

1 A 4 B London Road

Each component of the first line is in its own cell (ie 5 cells are used) and this would become..

1A London Road
2A London Road
3A London Road
4A London Road
1B London Road
2B London Road
3B London Road
4B London Road

If anybody has a potential solution to this problem, please let me know.

Thanks

Phil

 

[jindon]

What if

1 A 4 D London Road

?

 

[philipd]

1 A 4 D then the result would be

1 A
2 A
3 A
4 A
1 B
2 B
3 B
4 B
1 C
2 C
3 C
4 C
1 D
2 D
3 D
4 D

 

[facethegod]

Try

Option Explicit
Sub tst()
Dim aa() As String, r As Range, a
Dim i As Integer, ii As Integer, iii As Integer, x As Integer
Set r = Range("A1:E" & Cells(Rows.Count, 1).End(xlUp).Row): a = r
ReDim aa(1 To Rows.Count, 1 To 1)
For i = 1 To UBound(a, 1)
    For ii = Asc(a(i, 2)) To Asc(a(i, 4))
        For iii = a(i, 1) To a(i, 3)
            x = x + 1: aa(x, 1) = iii & Chr(ii) & " " & a(i, 5)
        Next
    Next
Next
r.Cells(1, r.Columns.Count).Offset(, 1).Resize(UBound(aa, 1), 1) = aa
End Sub

HTH