Excel:根据模式在每x行中插入新行，并包含内容 [英] Excel: Insert new line every x rows with content according to a pattern

问题描述

我在excel中有一长列，其中有两列.我想每隔26行插入两行新记录，将其余的行进一步往下推.所以基本上我有

I have a long list in excel with two columns. Every 26 rows, I want to insert two new lines, pushing the remaining rows further down. So basically I have

26   ABC
27   DEF
28   GHI

并想做到

26   ABC
27
28
29   DEF
30   GHI

此外，如果可能的话，我想根据一种简单的模式将文本放入这些新行中:

Additionally, if it's possible, I'd like to put text into those new lines according to a simple pattern:

1_sometext
1_someothertext

2_sometext
2_someothertext

因此，以"1_"开头的行将进入第27和28行，以"2_"开头的行将进入第55和56行，并且数字不断增加.

So the ones starting with "1_" would go into rows 27 and 28, the ones starting with "2_" would go into rows 55 and 56, and the number keeps incrementing.

推荐答案

一切皆有可能.您只需要为此编写VBA宏

Everything is possible. You just need to write VBA macro for this which should look like this one

Sub someSubForLazyGuy()

    Dim currentSheet As Worksheet
    Set currentSheet = Sheets("Sheet2")

    Dim rowCounter As Long
    Dim spaceCounter As Long

    rowCounter = 27
    spaceCounter = 1
    With currentSheet
        Do While .Cells(rowCounter, 1).Value <> ""
            .Cells(rowCounter, 1).EntireRow.Insert
            .Cells(rowCounter, 1).EntireRow.Insert
            .Cells(rowCounter, 1).Value = spaceCounter & "_sometext"
            .Cells(rowCounter + 1, 1).Value = spaceCounter & "_someothertext"
            spaceCounter = spaceCounter + 1
            rowCounter = rowCounter + 27 + 1
        Loop

    End With
End Sub

它的工作原理很简单.循环行，每27行插入2个新行.然后在这两行中填充文本和其他文本，将行计数器移动27行，并为新行加1，依此类推，直到列中有数据...

And it works just simple. You loop rows and every 27 row you insert 2 new rows. Then you fill your sometext and someother text on this two lines, shift row counter by 27 rows and plus 1 for your new row and so on until there are data in column...

这篇关于Excel:根据模式在每x行中插入新行，并包含内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！