0

I have an Excel sheet where two columns (say J and K) among data in sheet1 are as follows:

J ------------ K

1 ----------- 25

26 --------- 50

101 ------ 150

Which are some ranges. I have another sheet where I have column (let's say I), which I want to update it to have the values as indicated by the range; i.e. column values of I to be 1,2,3,...25,26,27,...50,101,102,103,...150 (vertically). Note that ranges in J-K are not always continuous (e.g. gap between 50 and 101) and not always in the right order.

I'm trying to find some automated way to achieve that, instead of manually copying the starting value of the range (from I) and then dragging down sequentially until the ending value (from J) for every individual I and J value, which is a very huge set of data. Is there any way to help doing that?

Note: I'm using Microsoft Excel 2013, but a solution for excel 2010 or 2007 is more than welcomed...

6
  • I'd start with private sub worksheet_change(byval target as range) Nov 6, 2013 at 1:02
  • Depending on how structured the data in Sheet1 is, you could solve this by using a formula. Is there a limit on the number of rows in Sheet1? Is there a limit on how many rows should be "generated" at maximum? Especially if you can answer the first question with "yes", there's a good chance a formula can be found.
    – Hauke P.
    Nov 6, 2013 at 10:33
  • @HaukeP. Actually it's fixed data, the ranges are the sequence of printed documents serial. It's fixed rows of what actually was printed, which isn't always have mathematical pattern. Did I get you right?
    – amyassin
    Nov 6, 2013 at 13:54
  • Ah, do you really need Excel to solve this? I guess, a simple piece of JavaScript code would do the job way better. (Or "just" VBA as well.)
    – Hauke P.
    Nov 6, 2013 at 18:58
  • @HaukeP. The data was submitted to me in Excel, plus there's some other stuff in the sheets that requires Excel. As a quick and dirty solution (to get my boss out of my head) I used a python script to do that after migrating the data to raw text file. But still interested in a way to do that without having to copy-paste a lot of data...
    – amyassin
    Nov 7, 2013 at 12:31

1 Answer 1

1
+50

This macro should do it:

Option Explicit

Sub FillSequence()
    Dim SrcSheet As Worksheet
    Set SrcSheet = Sheets("Sheet1")

    Dim SrcCol As Long
    Dim SrcRow As Long

    SrcCol = 10
    SrcRow = 1

    While Not IsEmpty(SrcSheet.Cells(SrcRow, SrcCol))
        Dim StartNumber As Long
        Dim EndNumber As Long

        StartNumber = SrcSheet.Cells(SrcRow, SrcCol)
        EndNumber = SrcSheet.Cells(SrcRow, SrcCol + 1)

        If EndNumber < StartNumber Then
            MsgBox "Input data wrong."
            Exit Sub
        End If

        Dim Number As Long

        For Number = StartNumber To EndNumber
            ActiveCell = Number
            ActiveCell.Offset(1, 0).Select ' This part could be optimized so
                                           ' that the selection does not have
                                           ' to be updated each time - but 
                                           ' this should suffice for the 
                                           ' moment. Just let me know if the
                                           ' performance is too bad for
                                           ' practical use.
        Next

        SrcRow = SrcRow + 1
    Wend

End Sub

It assumes that your numbers are on Sheet1 in columns J and K (or 10 and 11) and start in the first row. Simply run it while having selected the first cell where the sequence should start. HTH.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .