跳到主要內容

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

假設您具有一定範圍的數據,現在您想在值更改時在數據之間插入空白行,以便可以將一列中的順序相同值分開,如下面的屏幕截圖所示。 在本文中,我將為您解決一些技巧。

使用小計功能更改值時插入空白行

值隨輔助列更改時插入空白行

使用VBA代碼更改值時插入空白行

功能強大的值更改時,插入特定數量的空白行


使用小計功能更改值時插入空白行

隨著 總計 功能,您可以按以下步驟在值更改時在數據之間插入空白行:

1。 選擇您要使用的數據范圍。

2。 點擊 數據 > 總計 打開 總計 對話框,然後在 總計 對話框,請執行以下選擇:

1:根據值何時在以下更改,選擇要插入空白行的列名 每次變化 在部分;
2:選擇 計數 來自 使用功能 下拉列表;
3:檢查要在其中插入小計的列名 添加到小計 列錶框

3。 然後點擊 OK,小計線已插入到不同產品之間,並且大綱符號顯示在表的左側,請參見屏幕截圖:

4。 然後單擊輪廓符號頂部的數字2,僅顯示小計線。

5。 然後選擇小計範圍數據,然後按 Alt +; 快捷鍵僅選擇可見行,請參見屏幕截圖:

6。 僅選擇可見行後,然後按 刪除 鍵,所有小計行都已刪除。

7。 然後單擊任何其他單元格,然後返回 數據 > 取消組合 > 清除輪廓 刪除大綱,請參見屏幕截圖:

8。 輪廓符號立即被清除,當值更改時,您可以看到在數據之間插入了空白行,請參見屏幕截圖:

9。 最後,您可以根據需要刪除列A。


值快速變化時插入分頁符,空白行,底部邊框或填充顏色

如果你有 Excel的Kutools's 區分差異 功能,您可以根據需要更改值時快速插入分頁符,空白行,底部邊框或填充顏色。 請參見下面的演示。         點擊下載Kutools for Excel!


值隨輔助列更改時插入空白行

使用幫助器列,您可以先插入公式,然後再應用 查找和替換 函數,最後,在變化的值之間插入空白行。 請執行以下操作:

1。 在空白單元格C3中,請輸入此公式 = A3 = A2,然後在單元格D4中輸入此公式 = A4 = A3,請參見屏幕截圖:

2。 然後選擇C3:D4,並將填充手柄拖到您要應用這些公式的範圍內,您將獲得 or 在單元格中,請參見屏幕截圖:

3。 然後按 Ctrl + F 鍵打開 查找和替換 對話框,在彈出的對話框中,輸入 查找內容 下的文字框 發現 選項卡,然後單擊 選項 按鈕以展開此對話框,然後選擇 價值觀 來自 在看 下拉列表,請參見屏幕截圖:

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更改時,在數據之間插入了空白行。


功能強大的值更改時,插入特定數量的空白行

如果您嘗試使用上述麻煩的方法,在這裡,我將介紹一個有用的工具, Excel的Kutools's 區分差異 可以幫助您在單元格值快速而輕鬆地更改時插入分頁符,空白行,底部邊框或填充顏色。

提示:要應用此 區分差異 功能,首先,您應該下載 Excel的Kutools,然後快速輕鬆地應用該功能。

安裝後 Excel的Kutools,請這樣做:

1。 點擊 庫工具 > 格式 > 區分差異,請參見屏幕截圖:

2。 在 按關鍵列區分差異 對話框,請執行以下操作:

  • 選擇要使用的數據范圍,然後選擇要基於其插入空白行的鍵列;
  • 然後檢查 空白行 選項從 選項 部分,然後輸入要插入的空白行數。

3。 然後點擊 Ok 按鈕,並且如果單元格值發生更改,則將特定數量的空白行插入到數據中,請參見屏幕截圖:

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

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, Hassan,

This vba is amazing, whereas I need one more alternate code for insert single blank row after changes in sequencing numbers. Example:- In column having number series like 1, 2, 3, 5, 6, 9 & etc...
Need to add single blank row between 3 & 5 as well as 6 & 9.
Please can you with this.
This comment was minimized by the moderator on the site
Can the VBA method be adapted to ignore blank cells? I have a file which I need to insert rows in based on two different columns but when I run the macro on the second column I end up with three blank lines where the first macro run inserted rows.
Or can it run on two columns at the same time?
This comment was minimized by the moderator on the site
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??
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
[quote]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.By Hudson[/quote] Please let me know how to insert more than 1 row.
This comment was minimized by the moderator on the site
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???
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Where in the code would I need to modify to include more than one row, I need to add 10 after each break... Thanks
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations