Tutorial :How to create a list from beginning number and end number



Question:

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.


Solution:1

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »