CODEWITHSUNDEEP
excelvbalist
Antonius Saputra
Antonius Saputra

Reputation:

How to create a list from beginning number and end number

I have a set of numbers:

 |  A     B
--------------
1| 100   102
2| 103   103
3| 104   105
4| 106   110

Column A is the beginning number and Column B is the end number. We need to create a list (on a separate cell) of numbers using the beginning number and end number using column A & B. E.g. based on the 1st set of data from Row 1(A1 & B1) the 1st set of numbers will be: 100,101,102, then it will go to row 2, put 103 after 102 and move on to row 3, expanded the list and display 104 and 105, then to last row where it should list 105,106,107,108,109,110.

We should be able to mark the beginning of the number for the list so that we know the start of each list. i.e. all the number listed in Column A should be marked.

Upvotes: 1

Views: 5363

Answers (1)

Lunatik
Lunatik

Reputation: 3948

Not sure I completely understand your question, but I think you want to turn this:

100 102
103 103
104 105
106 110

into this?

100 102     100, 101, 102
103 103     103
104 105     104, 105
106 110     106, 107, 108, 109, 110

If so, the following code will achieve this:

Private Sub getListsOfNumbers()
    Dim inputRange As String
    Dim x As Long
    Dim y As Long

    'Get input range of data
    inputRange = InputBox("Enter input range", "Start", "A1:A4")

    'Clear output range (two column offset)
    Range(inputRange).Offset(0, 2).ClearContents

    With Range(inputRange)

        'Loop through input range
        For x = 1 To .Cells.Count

            'Loop through difference between second column and first column
            For y = 0 To (.Cells(x, 2) - .Cells(x, 1))

                'Add value to output column
                .Cells(x, 3) = .Cells(x, 3) & (.Cells(x, 1) + y) & ", "
            Next y

            'Tidy up output by removing trailling comma
            .Cells(x, 3) = CStr(Left(.Cells(x, 3), Len(.Cells(x, 3)) - 2))
        Next x
    End With
End Sub

If I've misread your request, please let me know.

Edit: Just tried this for real and, with larger datasets, it would be as slow as one might predict. If your data has 100s/1000s of rows, and/or the difference between the numbers in columns A & B is significantly larger than the example, then you'd probably want to look at minimising the delay by turning off calculation and screenUpdating at the beginning of the procedure and restoring once complete.

The Excel help has the syntax and examples to help you if you need to implement this.

Upvotes: 3

Related Questions