Work out numbers between two telephone ranges to use in a dropdown list.

[edwardtong694]

Hi All,

I was wondering if someone may be able to help...

I have a spreadsheet which stores telephone number ranges.

For example:

Range Start: Range End: Quantity
0800100100 0800100199 200

I am trying to think of a way where I can automatically list all of the numbers into a column so I can use them in a drop down list without the user having to manually type them all out.

The list will look something like:
0800100100
0800100101
0800100102
etc..

Can anyone think of a way to do this?

The telephone number fields are dynamic so will change as the user enters their telephone ranges.

Thanks in advance.

Ed

 

[mumps]

Assuming that 0800100100 is in cell A2 and that 0800100199 is in cell B2, try:

Sub AddOne()
    Dim x As Long
    Dim y As Long
    y = 4
    Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
    For x = 0 To Range("B2").Value - Range("A2").Value
        Range("A" & y) = Range("A2").Value + x
        y = y + 1
    Next x
End Sub

This will place the list in column A starting at row 4.

 

[edwardtong694]

Many thanks that works!!

Is it also possible to modify that slightly so that.. if there is multiple ranges i.e

Range Start: Range End: Quantity
0800100100 0800100199 100
0800200100 0800200199 100
0800300100 0800300199 100

That it will add all of the values into one long list in Collumn A?

Thanks

Ed

 

[mumps]

Try:

Sub AddOne()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim LastRow As Long
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row + 1
    Dim rng As Range
    Range("A" & bottomB & ":A" & Range("A" & Rows.Count).End(xlUp).Row + 1).ClearContents
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For Each rng In Range("A2:A" & LastRow)
        For x = 0 To rng.Offset(0, 1).Value - rng.Value
            Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = rng.Value + x
        Next x
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[edwardtong694]

Perfect!

Thanks so much!

Ed

 

[mumps]

You are very welcome.