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

or

如何在Excel中基于单元格值自动插入行?

DOC  - 插入 - 行基于对价值,1
假设您有一个数据范围,并且您想要在Excel中自动插入高于或低于特定值的空白行,例如,自动插入零值以下的行,如下面的屏幕截图所示。 在Excel中,没有直接的方法可以解决这个任务,但是我可以引入一个宏代码,让你在Excel中根据一定的值自动插入行。
使用VBA根据单元格值插入以下行

在Kutools for Excel中,根据单元格值插入上面的行 好idea3

在Excel中选择具有一个或两个条件的单元格/行/列

选择特定单元格的Kutools for Excel可以根据一个标准或两个标准快速选择范围内的所有单元格或行或列。 点击60天免费试用!
doc选择特定单元格1
Kutools for Excel:拥有超过300便利的Excel加载项,可以在60天免费试用,不受限制。

要通过运行VBA插入基于单元格值的行,请执行以下步骤:

公式 再也不用担心Excel中长的公式了! Kutools for Excel's 自动文本 可以将所有公式添加到组中
作为自动文本,并解放你的大脑! 点击这里了解自动文本 点击这里获得免费试用

1。 按 Alt + F11键 键,同时,和 Microsoft Visual Basic for Applications 窗口弹出。

2。 点击 插页 > 模块,然后粘贴到VBA代码下面弹出 模块 窗口。

VBA:根据单元格值插入下面的行。

Sub BlankLine()
	'Updateby20150203
	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)
	Set WorkRng                = WorkRng.Columns(1)
	xLastRow                   = WorkRng.Rows.Count
	Application.ScreenUpdating = False
	For xRowIndex = xLastRow To 1 Step - 1
		Set Rng                   = WorkRng.Range("A" & xRowIndex)
		If Rng.Value = "0" Then
			Rng.Offset(1, 0).EntireRow.Insert Shift: = xlDown
		End If
	Next
	Application.ScreenUpdating = True
End Sub

3。 点击 F5 钥匙还是 按钮,弹出一个对话框,并选择包含零的列。 看截图:
DOC  - 插入 - 行基于对价值,2

4。 点击 OK。 然后空白行将插入零值以下。
DOC  - 插入 - 行基于对价值,3

小技巧:

1。 如果你想插入其他值的行,你可以改变 0 以任何你想在VBA中的值: 如果Rng.Value =“0”那么.

2。 如果要插入大于零或其他值的行,可以使用下面的vba代码。

VBA:插入大于零值的行:

Sub BlankLine()
	'Updateby20150203
	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)
	Set WorkRng                = WorkRng.Columns(1)
	xLastRow                   = WorkRng.Rows.Count
	Application.ScreenUpdating = False
	For xRowIndex = xLastRow To 1 Step - 1
		Set Rng                   = WorkRng.Range("A" & xRowIndex)
		If Rng.Value = "0" Then
			Rng.EntireRow.Insert Shift: = xlDown
		End If
	Next
	Application.ScreenUpdating = True
End Sub

DOC  - 插入 - 行基于对价值,4


如果你不熟悉VBA,你可以试试 Kutools for Excel's 选择特定单元格 实用程序,然后插入上面的行。

Kutools for Excel, 与超过 300 方便的功能,让您的工作更轻松。

安装后 Kutools for Excel,请按照以下步骤操作:(免费下载Kutools for Excel!)

1。 选择要查找特定单元格的列表,然后单击 Kutools > 选择 > 选择特定单元格。 看截图:
doc根据值9插入行

2。 在弹出的对话框中,检查 整行 选项,然后去选择 等于 前往 具体类型 列表,然后在右侧文本框中输入要查找的值。 看截图:
doc根据值6插入行

3。 点击 Ok,弹出一个对话框提醒你所选择的行数,关闭它。

4。 将光标放在一个选定的行上,然后右键单击选择 插页 从上下文菜单。 看截图:
doc根据值7插入行

现在,根据特定的值插入上面的行。
doc根据值8插入行


相关文章:

选项卡式浏览和编辑多个Excel工作簿/ Word文档,如Firefox,Chrome,Internet浏览10!

您可能熟悉在Firefox / Chrome / IE中查看多个网页,并通过轻松单击相应的选项卡在它们之间切换。 此处,Office选项卡支持类似的处理,允许您在一个Excel窗口或Word窗口中浏览多个Excel工作簿或Word文档,并通过单击其选项卡轻松切换它们。 点击Office Tab的免费45天试用版!

擅长

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.
    Ludovico · 15 days ago
    Sub BlankLine()
    'Updateby20150203
    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)
    Set WorkRng = WorkRng.Columns(1)
    xLastRow = WorkRng.Rows.Count
    Application.ScreenUpdating = False
    For xRowIndex = xLastRow To 1 Step - 1
    Set Rng = WorkRng.Range("A" & xRowIndex)
    If Rng.Value = "0" Then
    Rng.EntireRow.Insert Shift: = xlDown
    End If
    Next
    Application.ScreenUpdating = True
    End Sub


    I need this to work everytime i put something in the cell, and also with more variables. What i mean is that if i put 2 in the cell, i need it to insert 2 row and not just 1.
  • To post as a guest, your comment is unpublished.
    safa · 4 months ago
    Hello, this is very helpful. What if I wanted to add two lines below and I wanted to more values. For instance, I want to add two lines after value 26/04/2019 and then two lines after 03/04/2019, and list goes on. How do I keep adding to the vba? Sorry im still a beginner. Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Gina · 9 months ago
    Hi, Thanks for this, however I have another scenario where I need to insert a cingle cell under the value that is not zero. Appreciate any suggestion.
  • To post as a guest, your comment is unpublished.
    Louis · 1 years ago
    Hi i am trying to use this code to enter a row when a the first 4 digits in a cell changes (if thats even possible)

    for example,
    2222A
    2222B
    2223K


    the line will be inserted after 2222B as the 3rd number is a 3 and not a 2

    Thanks guys!!
  • To post as a guest, your comment is unpublished.
    Hi · 1 years ago
    hi I just wanna ask how to add row if the codition is that add row should be done when a cell has a data already (It is for a excel workbook with a lot of sheets :) Thanks!
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      maybe this vba code can help you. It will add rows if above row is not empty

      Sub helping()
      Dim count As Long
      For count = ActiveSheet.UsedRange.Rows.count To 1 Step -1
      If Information.IsEmpty(Cells(count, 1)) = False Then Rows(count + 1).Insert
      Next count
      End Sub
  • To post as a guest, your comment is unpublished.
    ermias · 1 years ago
    Here is my question and it is a very difficult one i guess.. is there a vba code that add a new row below a filtered column and copy just the first three cells into the added new row and continue doing so until the user stops hitting "enter" and unfilter the filtered cells?
  • To post as a guest, your comment is unpublished.
    Tiago Dias · 1 years ago
    I need huge help on this subject. I have 2 columns, on the 1st I have my data time 01/01/2016 05:00:00, days/months/year hour/minute/seconds and in the 2 2nd column the respective data associated to the time.

    My problem is that I want to add data time between rows since I have days gaps. 1st line is 01/01/2016 and the 2nd row has, for example, 10/01/2016, so I have 9 days. and that code doesn't work for me.

    Looking forward to getting some feedback, please! Thanks
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      You can try this VBA

      Sub InsertValueBetween()
      'Update 20130825
      Dim WorkRng As Range
      Dim Rng As Range
      Dim outArr As Variant
      Dim dic As Variant
      Set dic = CreateObject("Scripting.Dictionary")
      'On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      num1 = WorkRng.Range("A1").Value
      num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
      interval = num2 - num1
      ReDim outArr(1 To interval + 1, 1 To 2)
      For Each Rng In WorkRng
      dic(Rng.Value) = Rng.Offset(0, 1).Value
      Next
      For i = 0 To interval
      outArr(i + 1, 1) = i + num1
      If dic.Exists(i + num1) Then
      outArr(i + 1, 2) = dic(i + num1)
      Else
      outArr(i + 1, 2) = ""
      End If
      Next
      With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
      .Value = outArr
      .Select
      End With
      End Sub


      Or if you have Kutools for Excel, you can try this function:
      • To post as a guest, your comment is unpublished.
        Tiago Dias · 1 years ago
        Hello again Sunny, i got some sucess on editing the code to this (i change the num1 line to A2 and With WorkRng.Range("A2:A100000").Resize(UBound(outArr, 1), UBound(outArr, 2)):


        Sub InsertValueBetween()
        'Update 20130825
        Dim WorkRng As Range
        Dim Rng As Range
        Dim outArr As Variant
        Dim dic As Variant
        Set dic = CreateObject("Scripting.Dictionary")
        'On Error Resume Next
        xTitleId = "KutoolsforExcel"
        Set WorkRng = Application.Selection
        Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
        num1 = WorkRng.Range("A2").Value
        num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
        interval = num2 - num1
        ReDim outArr(1 To interval + 1, 1 To 2)
        For Each Rng In WorkRng
        dic(Rng.Value) = Rng.Offset(0, 1).Value
        Next
        For i = 0 To interval
        outArr(i + 1, 1) = i + num1
        If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
        Else
        outArr(i + 1, 2) = ""
        End If
        Next
        With WorkRng.Range("A2:A100000").Resize(UBound(outArr, 1), UBound(outArr, 2))
        .Value = outArr
        .Select
        End With
        End Sub



        I show you the graphs, it doesn't work 100% because it doesn't create the time from A1 to A2
      • To post as a guest, your comment is unpublished.
        Tiago Dias · 1 years ago
        Thanks a lot, I have tried both, the 1st one since I have like 500 rows of data, I do that for the all 500 rows and doesn't do anything, I think perhaps it has a limitation on the rows to use, and when I select just the first 5 rows, for example, it doesn't create the missing rows, replaces the rows for the missing data.

        Another problem that I have is that my time data has also the Day/Month/Year HH: MM: SS
        • To post as a guest, your comment is unpublished.
          Tiago Dias · 1 years ago
          From 2 to 3, it creates the missing data that I want ok, but the value of the 03/01/2016 is eliminated and there is some time data that is eliminated something that I don't want either
          • To post as a guest, your comment is unpublished.
            Sunny · 1 years ago
            Sorry the VBA code did not help you, I cannot find the method which can work for date and time format. If you find the solution finnally, could you let me know? Thank you.
  • To post as a guest, your comment is unpublished.
    Faissal sardar · 1 years ago
    How Can I insert more than one row ?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      if you want to insert blank rows below, try this

      Sub BlankLine()
      'Updateby20150203
      Dim Rng As Range
      Dim WorkRng As Range
      Dim xInsertNum As Long
      ' On Error Resume Next
      xTitleId = "Kutools for Excel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      If WorkRng Is Nothing Then Exit Sub
      xInsertNum = Application.InputBox("The number of blank rows you want to insert", xTitleId, Type:=1)
      If xInsertNum = False Then
      MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
      Exit Sub
      End If
      Set WorkRng = WorkRng.Columns(1)
      xLastRow = WorkRng.Rows.Count
      Application.ScreenUpdating = False
      For xRowIndex = xLastRow To 1 Step -1
      Set Rng = WorkRng.Range("A" & xRowIndex)
      If Rng.Value = "0" Then
      Rng.Offset(1, 0).Resize(xInsertNum).EntireRow.Insert Shift:=xlDown
      End If
      Next
      Application.ScreenUpdating = True
      End Sub

      The below one is to insert rows above.
      • To post as a guest, your comment is unpublished.
        Joubero Lambrechts · 5 months ago
        HI Sunny, this macro works perfectly for me; i just had to change the quantity of rows to 30 and change the 0 to text: "Closing Balance". But now i want to copy paste a selection of cells which is 30 rows high into the 30 blank lines which were just inserted by this macro. Can you suggest a new macro (or an amendment to this one) to copy and paste a range into each 30blanks lines. I have named the range to copy and paste 'template'.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      You can try this VBA

      Sub BlankLine()
      'Updateby20150203
      Dim Rng As Range
      Dim WorkRng As Range
      Dim xInsertNum As Long
      ' On Error Resume Next
      xTitleId = "Kutools for Excel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      If WorkRng Is Nothing Then Exit Sub
      xInsertNum = Application.InputBox("The number of blank rows you want to insert ", xTitleId, Type:=1)
      If xInsertNum = False Then
      MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
      Exit Sub
      End If
      Set WorkRng = WorkRng.Columns(1)
      xLastRow = WorkRng.Rows.Count
      Application.ScreenUpdating = False
      For xRowIndex = xLastRow To 1 Step -1
      Set Rng = WorkRng.Range("A" & xRowIndex)
      If Rng.Value = "0" Then
      Rng.Resize(xInsertNum).EntireRow.Insert Shift:=xlDown
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    pranav · 1 years ago
    That was amaaaazing!!. Thanks man.
  • To post as a guest, your comment is unpublished.
    Isidora · 1 years ago
    I love you. Thank you.
  • To post as a guest, your comment is unpublished.
    yatorres90 · 2 years ago
    I want to insert rows based on a count using a cell value in one spreadsheet and inserting rows in another spreadsheet.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Thanks to your message. But can you discribe your question with more details? What rows do you want to insert? Blank? And Where you want to insert at in the sheet? If you can, give me some screenshhot. Thank you.
  • To post as a guest, your comment is unpublished.
    Rahul · 2 years ago
    Hi,
    I want to insert multiple rows based on the value
    Ex: I want to insert 1 blank row below the cell with value 2, 2 rows below the cell with value 3, 3 rows below the cell with value 4 and so on

    Can you please help me with this?
    • To post as a guest, your comment is unpublished.
      Ashley · 2 years ago
      DId you ever get an answer to this? I'm trying to do the same thing.

      Have a list of employees with # of weeks vacation they get. I want to insert a row for each week. It will be 1, 2 or 3 rows depending on how much time they've earned. the #s 1 2 3 are already in my spreadsheet.
  • To post as a guest, your comment is unpublished.
    Jafar · 4 years ago
    I want to paste specific content under below cell. How to do that? Instead of Blank row, I want to insert value in few columns.