提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中以固定間隔插入特定行數?

在Excel工作表中,您可以使用插入功能在現有行之間插入空白行。 但是,如果您有大量的數據,並且需要在每三行之後插入兩行空白行,那麼您如何快速方便地完成這項工作?

用VBA代碼以固定間隔將特定行數插入數據

使用Kutools for Excel以固定間隔將特定行數插入數據


以固定的時間間隔將特定數量的行或列插入數據中:

Kutools for Excel插入空白行和列 可以幫助您將每隔一行或第n行或列插入現有數據。

DOC  - 插入 - 行 - 值的變化,17

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!



以下VBA代碼可以幫助您在現有數據中的第n行之後插入特定數量的行。 請做如下操作:

1。 按住 ALT + F11 在Excel中的鍵,它打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,並將以下代碼粘貼到 模塊窗口.

VBA代碼:以固定的時間間隔將特定行數插入數據

Sub InsertRowsAtIntervals()
'Updateby20150707
Dim Rng As Range
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
    xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
    Application.Selection.EntireRow.Insert
    xNum1 = xNum1 + xNum2
Next
End Sub

3. 粘貼此代碼後,請按 F5 鍵來運行這段代碼,彈出一個提示框讓你選擇你想插入空行的數據范圍,看截圖:

doc以1的間隔插入行

4。 點擊 OK 按鈕,彈出另一個提示框,請輸入行間隔數,參見截圖:

doc以2的間隔插入行

5。 繼續點擊 OK 按鈕,在彈出的提示框中,請輸入要插入的空白行數,請參閱屏幕截圖:

doc以3的間隔插入行

6。 然後點擊 OK,並且空行已定期插入到現有數據中,請參見截圖:

doc以4的間隔插入行


如果您不熟悉上述VBA代碼,Kutools for Excel也可以幫助您,它的插入空白行和列功能可以快速方便地以固定的時間間隔將特定數量的行或列插入到現有數據中。

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

安裝後 Kutools for Excel請按照以下步驟進行:

1。 選擇要間隔插入空行的數據范圍。

2。 點擊 Kutools > 插入 > 插入空白行和列,看截圖:

doc以間隔5 5插入行

3。 在 插入空行和列 對話框中選擇 空行 or 空白列 你需要,從 插入類型,然後指定要使用的間隔行數和空行數,如下面的屏幕截圖所示:

doc以6的間隔插入行

4。 然後點擊 OK 按鈕,並且空白行已按特定時間間隔插入到所選範圍中,如下面的屏幕截圖所示:

doc以7的間隔插入行

點擊了解更多關於此插入空白行和列實用程序。

立即下載並免費試用Kutools for Excel!


Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


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.
    PK · 8 months ago
    hi how can I get the code for Insert specific number of columns into data at fixed intervals
    • To post as a guest, your comment is unpublished.
      skyyang · 8 months ago
      Hello, PK,
      To insert blank columns into existing data at specific intervals, the below VBA code can help you! Please try it.

      Sub InsertColumnsAtIntervals()
      Dim Rng As Range
      Dim xInterval As Integer
      Dim xCs As Integer
      Dim xCCount As Integer
      Dim xNum1 As Integer
      Dim xNum2 As Integer
      Dim WorkRng As Range
      Dim xWs As Worksheet
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      xCCount = WorkRng.Columns.Count
      xInterval = Application.InputBox("Enter column interval. ", xTitleId, 1, Type:=1)
      xCs = Application.InputBox("How many columns to insert at each interval? ", xTitleId, 1, Type:=1)
      xNum1 = WorkRng.Column + xInterval
      xNum2 = xCs + xInterval
      Set xWs = WorkRng.Parent
      For I = 1 To Int(xCCount / xInterval)
      xWs.Range(xWs.Cells(WorkRng.Row, xNum1 + xCs - 1), xWs.Cells(WorkRng.Row, xNum1)).Select
      Application.Selection.EntireColumn.Insert
      xNum1 = xNum1 + xNum2
      Next
      End Sub
  • To post as a guest, your comment is unpublished.
    Martin · 1 years ago
    Fantastic - you saved me a lot of mindless data entry, thank you very much
  • To post as a guest, your comment is unpublished.
    Manish · 1 years ago
    Hi


    I use interval vba code its working..But when I use above 100000 rows it not working.. kindly suggest what should I change if any.


    Sub InsertRowsAtIntervals()
    'Updateby20150707
    Dim Rng As Range
    Dim xInterval As Integer
    Dim xRows As Integer
    Dim xRowsCount As Integer
    Dim xNum1 As Integer
    Dim xNum2 As Integer
    Dim WorkRng As Range
    Dim xWs As Worksheet
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    xRowsCount = WorkRng.Rows.Count
    xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
    xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
    xNum1 = WorkRng.Row + xInterval
    xNum2 = xRows + xInterval
    Set xWs = WorkRng.Parent
    For i = 1 To Int(xRowsCount / xInterval)
    xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
    Application.Selection.EntireRow.Insert
    xNum1 = xNum1 + xNum2
    Next
    End Sub
  • To post as a guest, your comment is unpublished.
    Atif · 2 years ago
    Thanks AloT!!
  • To post as a guest, your comment is unpublished.
    Stephanie · 2 years ago
    thank you so much!!!!! this is amazing
  • To post as a guest, your comment is unpublished.
    Farooq · 2 years ago
    Hi,

    I'm using your code (below) can you please tell me how can fill those rows with custom text into it. I've used your code to enter three rows it worked perfectly but now I need to enter the text

    Row1 = Date
    Row2.= Location
    Row3 = Phone Number

    Thanks in advance...



    "Sub InsertRowsAtIntervals()
    'Updateby20150707
    Dim Rng As Range
    Dim xInterval As Integer
    Dim xRows As Integer
    Dim xRowsCount As Integer
    Dim xNum1 As Integer
    Dim xNum2 As Integer
    Dim WorkRng As Range
    Dim xWs As Worksheet
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    xRowsCount = WorkRng.Rows.Count
    xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
    xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
    xNum1 = WorkRng.Row + xInterval
    xNum2 = xRows + xInterval
    Set xWs = WorkRng.Parent
    For i = 1 To Int(xRowsCount / xInterval)
    xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
    Application.Selection.EntireRow.Insert
    xNum1 = xNum1 + xNum2
    Next
    End Sub"