vba 如何在一组数据后自动插入一个空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15417544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to automatically insert a blank row after a group of data
提问by Ben
I have created a sample table below that is similar-enough to my table in excel that it should serve to illustrate the question. I want to simply add a row after each distinct datum in column1 (simplest way, using excel, thanks).
我在下面创建了一个示例表，它与我在 excel 中的表非常相似，可以用来说明问题。我只想在 column1 中的每个不同数据后添加一行（最简单的方法，使用 excel，谢谢）。
_
_
CURRENT TABLE:
当前表：
column1 | column2 | column3
----------------------------------
A | small | blue
A | small | orange
A | small | yellow
B | med | yellow
B | med | blue
C | large | green
D | large | green
D | small | pink
_
_
DESIRED TABLE
想要的桌子
Note:the blank row after each distinct column1
注意：每个不同 column1 后面的空白行
column1 | column2 | column3
----------------------------------
A | small | blue
A | small | orange
A | small | yellow
B | med | yellow
B | med | blue
C | large | green
D | large | green
D | small | pink
回答by Our Man in Bananas
This does exactly what you are asking, checks the rows, and inserts a blank empty row at each change in column A:
这正是您所要求的，检查行，并在 A列中的每次更改时插入一个空白行：
sub AddBlankRows()
'
dim iRow as integer, iCol as integer
dim oRng as range
set oRng=range("a1")
irow=oRng.row
icol=oRng.column
do
'
if cells(irow+1, iCol)<>cells(irow,iCol) then
cells(irow+1,iCol).entirerow.insert shift:=xldown
irow=irow+2
else
irow=irow+1
end if
'
loop while not cells (irow,iCol).text=""
'
end sub
I hope that gets you started, let us know!
我希望这能让你开始，让我们知道！
Philip
菲利普
回答by pnuts
Select your array, including column labels, DATA > Outline -Subtotal, At each change in: column1, Use function: Count, Add subtotal to: column3, check Replace current subtotals and Summary below data, OK.
选择您的数组，包括列标签，DATA > Outline -Subtotal，在每次更改时：column1，使用函数：Count，将小计添加到：column3，选中 Replace current subtotals 和 Summary below data，OK。
Filter and select for Column1, Text Filters, Contains..., Count, OK. Select all visible apart from the labels and delete contents. Remove filter and, if desired, ungroup rows.
过滤并选择 Column1、Text Filters、Contains...、Count、OK。选择除标签外的所有可见内容并删除内容。删除过滤器，如果需要，取消组合行。
回答by Arunas
This won't work if the data is not sequential (1 2 3 4 but 5 7 3 1 5) as in that case you can't sort it.
如果数据不是连续的（1 2 3 4 但 5 7 3 1 5），这将不起作用，因为在这种情况下您无法对其进行排序。
Here is how I solve that issue for me:
以下是我为我解决这个问题的方法：
Column A initial data that needs to contain 5 rows between each number - 5 4 6 8 9
列 A 初始数据，每个数字之间需要包含 5 行 - 5 4 6 8 9
Column B - 1 2 3 4 5 (final number represents the number of empty rows that you need to be between numbers in column A) copy-paste 1-5 in column B as long as you have numbers in column A.
B 列 - 1 2 3 4 5（最终数字表示您需要在 A 列中的数字之间的空行数）只要您在 A 列中有数字，就在 B 列中复制粘贴 1-5。
Jump to D column, in D1 type 1. In D2 type this formula - =IF(B2=1,1+D1,D1)Drag it to the same length as column B.
跳转到 D 列，在 D1 类型 1 中。在 D2 中键入此公式 -=IF(B2=1,1+D1,D1)将其拖动到与 B 列相同的长度。
Back to Column C - at C1 cell type this formula - =IF(B1=1,INDIRECT("a"&(D1)),""). Drag it down and we done. Now in column C we have same sequence of numbers as in column A distributed separately by 4 rows.
回到 C 列 - 在 C1 单元格中键入此公式 - =IF(B1=1,INDIRECT("a"&(D1)),"")。把它拖下来，我们就完成了。现在在 C 列中，我们有与 A 列中相同的数字序列，分别由 4 行分布。
回答by Megan
Just an idea, if you know the categories, as small, medium, and large mentioned above...
只是一个想法，如果你知道类别，如上面提到的小、中和大......
At the bottom of the sheet, make 3 rows that only say small, medium, and large, change the font to white, and then sort so that it alphabetizes, placing a blank row between each section.
在工作表的底部，制作 3 行，仅表示小、中和大，将字体更改为白色，然后按字母顺序排序，在每个部分之间放置一个空白行。
回答by Kartik Kumar
- Insert a column at the left of the table 'Control'
- Number the data as 1 to 1000 (assuming there are 1000 rows)
- Copy the key field to another sheet and remove duplicates
- Copy the unique row items to the main sheet, after 1000th record
- In the 'Control' column, add number 1001 to all unique records
- Sort the data (including the added records), first on key field and then on 'Control'
- A blank line (with data in key field and 'Control') is added
- 在表格“控制”的左侧插入一列
- 将数据编号为 1 到 1000（假设有 1000 行）
- 将关键字段复制到另一个工作表并删除重复项
- 在第 1000 条记录后将唯一行项目复制到主表
- 在“控制”列中，将数字 1001 添加到所有唯一记录
- 对数据（包括添加的记录）进行排序，首先在关键字段上，然后在“控制”上
- 添加了一个空行（在关键字段和“控制”中包含数据）
回答by Ben
Figured it out.
弄清楚了。
Step 1
第1步
Put a new column to the left of column1 and copy+paste the following formula
在 column1 的左侧放置一个新列并复制+粘贴以下公式
=B2=B3
=B2=B3
=B3=B4
=B3=B4
=B4=B5
=B4=B5
... all the way to the bottom (assume column B here is column1 in the original question).
...一直到底部（假设这里的 B 列是原始问题中的 column1）。
This formula evaluates whether or not the next row is a new value in column1. Deopending on the result, you will have TRUE or FALSE. Copy and Paste these results as values and then swap "FALSE" for nil and "TRUE" for 0.5
此公式评估下一行是否是 column1 中的新值。根据结果，您将有 TRUE 或 FALSE。将这些结果复制并粘贴为值，然后将“FALSE”替换为 nil，将“TRUE”替换为 0.5
Step 2
第2步
Then add that column full of only 0.5's to the column1 which will yield the following table:
然后将只有 0.5 的那一列添加到 column1，这将产生下表：
newcolumn0 | column1 ("B") | column2 | column3
-----------------------------------------------------
| 1 | small | blue
| 1 | small | orange
1.5 | 1 | small | yellow
| 2 | med | yellow
2.5 | 2 | med | blue
3.5 | 3 | large | green
| 4 | large | green
4.5 | 4 | small | pink
Step 3
第 3 步
Lastly, copy and paste the values from newcolumn0 right below the values in column1 and then sort the table by column1 and you should have a blank row in between each distinct whole number in column1, with the table something like this:
最后，将 newcolumn0 中的值复制并粘贴到 column1 中的值正下方，然后按 column1 对表格进行排序，并且在 column1 中的每个不同整数之间应该有一个空白行，表格如下所示：
newcolumn0 | column1 ("B") | column2 | column3
---------------------------------------------------------------
| 1 | small | blue
| 1 | small | orange
1.5 | 1.5 | |
| 1 | small | yellow
| 2 | med | yellow
| 2 | med | blue
2.5 | 2.5 | |
| 3 | large | green
3.5 | 3.5 | |
| 4 | large | green
| 4 | small | pink
4.5 | 4.5 | |
Alternative Solutions (still no VBA)
替代解决方案（仍然没有 VBA）
- Put a value of 1 Column 1, Row 2 (assume this is A2)
- Put this formula in A3
=IF(B3=B2,A2,A2+1)and copy+paste this formula for the rest of column 2 - Then copy and paste all the values from column 1 into a new temp excel sheet, remove duplicates, then add 0.5 to all numbers, then paste these values below the values in original spreadsheet below the data in column 1, paste all data in column as values and then sort by that column, delete the temp excel sheet
- 输入 1 Column 1, Row 2 的值（假设这是 A2）
- 将此公式放在 A3 中
=IF(B3=B2,A2,A2+1)并复制+粘贴此公式用于第 2 列的其余部分 - 然后将第 1 列中的所有值复制并粘贴到新的临时 excel 表中，删除重复项，然后将 0.5 添加到所有数字，然后将这些值粘贴到原始电子表格中的值下方第 1 列中的数据下方，将列中的所有数据粘贴为值，然后按该列排序，删除临时 Excel 表
回答by Panicker
I have a large file in excel dealing with purchase and sale of mutual fund units. Number of rows in a worksheet exceeds 4000. I have no experience with VBA and would like to work with basic excel. Taking the cue from the solutions suggested above, I tried to solve the problem ( to insert blank rows automatically) in the following manner:
我在excel中有一个大文件处理共同基金单位的购买和销售。工作表中的行数超过 4000。我没有使用 VBA 的经验，想使用基本的 excel。从上面建议的解决方案中获取线索，我尝试以下列方式解决问题（自动插入空白行）：
- I sorted my file according to control fields
- I added a column to the file
- I used the "IF" function to determine when there is a change in the control data .
- If there is a change the result will indicate "yes", otherwise "no"
- Then I filtered the data to group all "yes" items
- I copied mutual fund names, folio number etc (no financial data)
- Then I removed the filter and sorted the file again. The result is a row added at the desired place. (It is not entirely a blank row, because if it is fully blank, sorting will not place the row at the desired place.)
- After sorting, you can easily delete all values to get a completely blank row.
- 我根据控制字段对文件进行了排序
- 我在文件中添加了一列
- 我使用“IF”函数来确定控制数据何时发生变化。
- 如果有变化，结果将显示“是”，否则“否”
- 然后我过滤数据以将所有“是”项目分组
- 我复制了共同基金名称、对开编号等（无财务数据）
- 然后我删除了过滤器并再次对文件进行了排序。结果是在所需位置添加了一行。（它不是完全空白的行，因为如果它是完全空白的，排序将不会将该行放置在所需的位置。）
- 排序后，您可以轻松删除所有值以获得完全空白的行。
This method also may be tried by the readers.
读者也可以尝试这种方法。
