vba Excel：连续数字+指定的开始和结束值+向下移动列数据

Excel: Sequential numbers + specified start and end values + shift column data down

提问by Brooks

I have a start value in column "A" (02) And an end value in column "B" (06)

我在“A”列（02）中有一个起始值，在“B”列（06）中有一个结束值

In column "C" I'd like to list the range of number sequentially using the start and end values in columns "A" and "B". However I'd also like to shift the cell data down to stop any data overlapping.

在“C”列中，我想使用“A”和“B”列中的开始和结束值顺序列出数字范围。但是，我还想将单元格数据向下移动以阻止任何数据重叠。

To go one step further, I don't know if this is possible, but it would help if the data from the original row could be duplicated in each of the new rows that have been created with the sequential values.

更进一步，我不知道这是否可行，但是如果可以在使用顺序值创建的每个新行中复制来自原始行的数据，这将有所帮助。

Edit:

编辑：

This code creates the sequential number specifying ONE start and end value cell, and doesn't shift the cells down.. this is where I'm at so far.

这段代码创建了指定一个起始值和结束值单元格的序列号，并且不会将单元格向下移动..这是我目前所处的位置。

Sub Serial_numbers()

startNumber = [A1].Value

endNumber = [B1].Value

For i = startNumber To endNumber

ActiveCell.Offset(i - startNumber, 0) = i

Next i

End Sub

Here's a pictured example of what I'm trying to do:

这是我正在尝试做的一个图片示例：

1. Initial Data:

1. 初始数据：

1. Required Output:

2. 所需输出：

采纳答案by jacouh

Do you want this?

你想要这个吗？

Sub sof20143262Serial_numbers()
  Dim i, iStep, j, jp1, startNumber, endNumber, delta
  Dim bEmpty As Boolean
  Dim strRange

'
  Application.ScreenUpdating = False
'
' intialize empty row key and set the first data row number j:
'
  bEmpty = False
  j = 2
'
' we use temporary memory for CPU time gain:
'   jp1: j + 1
'   strRange : range name
'
  Do While (Not bEmpty)
    jp1 = j + 1
    strRange = "A" & j
'
    startNumber = Range(strRange).Value
    endNumber = Range("B" & j).Value
    bEmpty = IsEmpty(startNumber)
'
'   terminate the loop if empty row:
'
    If (bEmpty) Then
      Exit Do
    End If
'
'   get number of rows to add:
'
    delta = endNumber - startNumber
    If (endNumber < startNumber) Then
      iStep = 1
      delta = -delta
    Else
      iStep = -1
    End If
'
    Range("C" & j).Value = startNumber
    endNumber = endNumber + iStep
'
'   insert a row and copy the right side data columns D to E,
'   here you can add more columns by changing E to any other letter:
'
    For i = endNumber To startNumber Step iStep
      Range(strRange).Offset(1).EntireRow.Insert shift:=xlDown
      Range("C" & jp1).Value = i - iStep
      Range("D" & jp1 & ":" & "E" & jp1).Value = Range("D" & j & ":" & "E" & j).Value
    Next
'
'   prepare the next loop:
'
    j = j + delta + 1
'
  Loop
'
  Application.ScreenUpdating = True

End Sub

Initial data: =====>

初始数据： =====>

Ending data:

结束数据：