提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中更改值时插入空白行?

假设您有一系列数据,现在您希望在值更改时在数据之间插入空白行,以便您可以按照以下屏幕截图在一列中分隔顺序相同的值。 在这篇文章中,我会谈一些技巧来解决这个问题。

当使用小计功能更改值时插入空白行

当值随辅助列更改时插入空白行

用VBA代码更改值时插入空白行

使用强大功能更改值时插入特定数量的空白行


当值快速变化时,插入分页符,空白行,底部边框或填充颜色

如果你有 Kutools for Excel's 区分差异 功能,您可以根据需要快速插入分页符,空白行,底部边框或填充颜色。 请参阅以下演示。 点击下载Kutools for Excel!


当使用小计功能更改值时插入空白行

随着 小计金额 功能,当值更改时,您可以在数据之间插入空白行,步骤如下:

1。 选择您要使用的数据范围。

2。 点击 数据 > 小计金额 打开 小计金额 对话框,并在 小计金额 对话框,请执行以下操作:

1:根据值的更改时间,选择要插入空行的列名称 每次更改 部分;
2:选择 计数 来自 使用功能 下拉列表;
3:在列表中选中要插入小计的列名称 将小计添加到 列表框

3。 然后点击 OK,小计行已插入到不同的产品之间,并且大纲符号显示在表格的左侧,请参阅截图:

4。 然后点击大纲符号顶部的数字2,仅显示小计行。

5。 然后选择小计范围数据,然后按 Alt +; 快捷键仅用于选择可见行,请参阅截图:

6。 选择可见的行后,只需按 删除 键,并且所有小计行都被删除。

7。 然后点击其他单元格,然后返回 数据 > 取消组合 > 清除大纲 要删除轮廓,请参阅屏幕截图:

8。 大纲符号已被清除,您可以在值更改时看到数据之间插入空行,请参见屏幕截图:

9。 最后,您可以根据需要删除列A.


当值随辅助列更改时插入空白行

使用助手列,您可以先插入公式,然后应用该公式 查找和替换 函数,最后在变化值之间插入空白行。 请做如下操作:

1。 在空白单元格C3中,请输入此公式 = A3 = A2,在单元格D4中输入此公式 = A4 = A3,看截图:

2。 然后选择C3:D4,然后将填充手柄拖动到您要应用这些公式的范围内,您将获得 or 在单元格中,看截图:

3。 然后按 Ctrl + F 键打开 查找和替换 对话框中,在弹出的对话框中输入 FALSE查找内容 文本框下 查找 选项卡,然后单击 期权 按钮展开该对话框,然后选择 价值 来自 在看 下拉列表,看截图:

4. 点击 找到所有 按钮,然后按 按Ctrl + A 要选择所有查找结果,一次选择所有FALSE单元格,请参见屏幕截图:

6。 关上 查找和替换 对话框,下一步,您可以通过单击插入空行 主页 > 插页 > 插入工作表行,当值根据列A进行更改时,空白行已插入数据中,请参阅截图:

7。 最后,您可以根据需要删除辅助列C和D.


用VBA代码更改值时插入空白行

如果您厌倦了使用上述方法,则此代码还可以帮助您一次性在更改的值之间插入空白行。

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications窗口.

2。 点击 插页 > 模块,并将以下代码粘贴到 模块窗口.

VBA代码:当值更改时插入空白行

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3。 然后按 F5 键以运行此代码,并且会弹出一个提示框,让您选择一个列数据,当您更改基于此值时,您想要插入空行,请参阅截图:

4。 然后点击 OK,当值根据列A而变化时,空白行已插入数据之间


使用强大功能更改值时插入特定数量的空白行

如果您尝试使用上述麻烦的方法,在这里,我将介绍一个有用的工具, Kutools for Excel's 区分差异 可以帮助您快速轻松地更改单元格值时插入分页符,空白行,底部边框或填充颜色。

提示:申请这个 区分差异 功能,首先,你应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请这样做:

1。 点击 Kutools > 格式 > 区分差异,看截图:

2。 在 按键列区分差异 对话框,请执行以下操作:

  • 选择要使用的数据范围,然后根据要选择要插入空行的键列;
  • 然后检查 空白行 选项从 期权 部分,并输入要插入的空白行数。

3。 然后点击 Ok 按钮,如果单元格值发生更改,则已将特定的空白行数插入到数据中,请参见屏幕截图:

点击下载Kutools for Excel和免费试用版吧!


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hassan · 10 months ago
    Hello,
    Is very usefull in case i need to insert 1 row, but if i need to insert 145 rows in every time the data change in spwcific column, how can i do it??
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hi, Hassan,
      To insert multiple blank rows when value changes in a specific column, you should apply the following VBA code:

      Note: In the below code, you should change the number 99 to your need, for example, when you insert 145 blank rows, you should change the number 99 to 144. Please try it, hope it can help you!

      Sub InsertRowsAtValueChange()
      Dim Rng As Range
      Dim WorkRng As Range
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      Application.ScreenUpdating = False
      For i = WorkRng.Rows.Count To 2 Step -1
      If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
      Range(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        DCalk · 10 months ago
        Very helpful. The code that skyyang shows above worked perfectly. Just make sure that the data doesn't already have spaces in it.

        I don't understand VBA, but I believe if you wanted to add more rows underneath data that already had the spacing, there should be a way to ignore spaces.

        Could a line be added to ignore or skip over blank lines? That might make this code more universal and repeatable if needed. Also a delete function that is similar to this may be useful so undo isn't necessary.
  • To post as a guest, your comment is unpublished.
    Suzi · 11 months ago
    RE: insert blank rows when value changes with vba code
    Is there a way that I can save the Range & not have to pick it every time I run it?
  • To post as a guest, your comment is unpublished.
    Eric F · 1 years ago
    The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Steve · 1 years ago
    I've been using my own solution for some time.
    1. Insert a helper column into Column A
    2. In A2, type "if(B2=B1,A1,A1+1)"
    3. Copy that formula down to the last row
    4. Copy all the populated cells in column A and Paste Special (Values) over them
    5. Copy all the cells again and paste them into column A in the first unpopulated cell (e.g. if you have 104 rows of data plus a header row you would paste into cell A106)
    6. Click on Data and Remove Duplicates (only on the cells you just pasted in Step 5; not on all rows)
    7. Sort all of Column A
    8. Delete Column A

    Seems like a lot of steps but only takes a few seconds.
  • To post as a guest, your comment is unpublished.
    Madhur · 2 years ago
    [quote name="Hudson"]Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.[/quote]

    Please let me know how to insert more than 1 row.
  • To post as a guest, your comment is unpublished.
    Davis · 2 years ago
    Hi there,

    These are almost useful! The first method doesn't work for me because when I follow the steps explicitly, the the data that I delete in the subtotal panes deletes the entire columns that I've sorted.

    In the second method when I get to the step where I insert sheet rows, the rows are inserted ABOVE the FALSE cells which breaks up the data, but the last selection of every group is then added to the group below.

    Any advice???
  • To post as a guest, your comment is unpublished.
    Hudson · 3 years ago
    Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.
  • To post as a guest, your comment is unpublished.
    Rafael · 3 years ago
    Where in the code would I need to modify to include more than one row, I need to add 10 after each break...

    Thanks
  • To post as a guest, your comment is unpublished.
    Ivo Tamm · 3 years ago
    Thank you very much for describing multiple solutions for the problem.
    I used the Visual Basic one and changed
    [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] with
    [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlMedium
    End With[/quote]
    This will draw lines between rows with changing values instead of empty lines.
  • To post as a guest, your comment is unpublished.
    Saul · 4 years ago
    Thank you so much, looked everywhere and this was the only solution that worked for me that was also in plain english.

    All the other Excel tutorial websites didn't break it down step-by-step they way you did, thank you very much.

    cheers : )
  • To post as a guest, your comment is unpublished.
    Abner · 4 years ago
    I have a column with the numbers 5,4,3,2 and 1 (a LOT of them) and I wanna separate the Fives from the others, like:

    5
    5
    5

    4
    3
    2
    1

    5
    5
    5
    5
    5

    4
    3
    2
    1

    The numbers are in desc order and there are always a four below a five,
    so I tried:

    Sub InsertRowsAtValueChange()
    'Update 20140716
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value = 5 And WorkRng.Cells(i - 1, 1).Value = 4 Then
    WorkRng.Cells(i, 1).EntireRow.Insert
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    But it didn't work. Can you help me?