How to insert a blank row after specific text in Excel?
If there are multi-line texts in a cell, now, you want to extract and copy the first or the last line text from the cell as the following screenshot shown. How could you deal with this job quickly and easily in Excel?
You can apply the below VBA code to insert blank rows after the specific text you need, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Insert blank rows after the specific text
Sub Insertrowbelow() 'updateby Extendoffice Dim i As Long Dim xLast As Long Dim xRng As Range Dim xTxt As String On Error Resume Next xTxt = Application.ActiveWindow.RangeSelection.Address Set xRng = Application.InputBox("please select the column with specific text:", "Kutools for Excel", xTxt, , , , , 8) If xRng Is Nothing Then Exit Sub If (xRng.Columns.Count > 1) Then MsgBox "the selected range must be one column", , "Kutools for Excel" Exit Sub End If xLast = xRng.Rows.Count For i = xLast To 1 Step -1 If InStr(1, xRng.Cells(i, 1).Value, "In progressing") > 0 Then Rows(xRng.Cells(i + 1, 1).Row).INSERT shift:=xlDown End If Next End Sub
Note: In the above code, you can change the text value “In progressing” to any other values that you desired from the script If InStr(1, xRng.Cells(i, 1).Value, "In progressing") > 0 Then.
3. Then, press F5 key to run this code, in the popped out dialog box, select the column data contains the specific text you want to use, see screenshot:
4. And then, click OK button, and the blank rows have been inserted after the certain text you specified, see screenshot:
More relative insert rows articles:
- Copy And Insert Row Multiple Times Or Duplicate The Row X Times
- In your daily work, have you ever tried to copy a row or each row and then insert multiple times below the current data row in a worksheet? For example, I have a range of cells, now, I want to copy each row and paste them 3 times to the next row as following screenshot shown. How could you deal with this job in Excel?
- Insert Blank Rows When Value Changes In Excel
- Supposing you have a range of data, and now you want to insert blank rows between the data when value changes, so that you can separate the sequential same values in one column as following screenshots shown. In this article, I will talk about some tricks for you to solve this problem.
- Insert Multiple Rows Or Columns Quickly In Google Sheets
- This article, I will introduce an easy way to insert multiple blank rows or columns in a google sheet.
- Insert Blank Row Above Specific Text In Excel
- When you work on a worksheet, have you ever tried to insert blank row above a specific text in a column? For example, there are some names in column A, now, I want to insert blank rows above the cells which contain the name “Mike” as the left screenshot shown, maybe, there are no direct way for you to solve this task in Excel. This article, I will talk about some good tricks for dealing with this job in Excel.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 months agoThis is so helpful! But how do I add new rows after several different texts. For example, I want to add a row after SB2 and BB2, is this possible? or do I just need to run it two times with two different texts?