How to insert (or create) new rows under every row that meets a certain condition?

[khjhzw]

Hello,

Thank you for reading my question. Could you please help me with this task?

I have to do a repetitive task with a quite a large excel worksheet (or table). I am wondering if there is way for me to do all this task at once or to save as much time as possible.

I want to insert (create/add) new rows under every row that meets a certain condition. To better understand what I am trying to do, please see the example below.

Suppose I have following worksheet

	A	B	C
1	chapter 1
2		length	15 pages
3		things to try	p10-11
4		things to try	p13
5	chapter 2
6		length	5 pages
7		things to try	p16
8	chapter 3
9		length	15 pages
10	chapter 4
11		length	20 pages
12		things to try	p43

I want to insert 2 new rows under every "chapter", that is under every row where there is an entry in column A. After that I want to add in each of the new rows a new entry, "word count" and "date started" respectively, in column B. The desired end result should look like the table below.

	A	B	C
1	chapter 1
		word count
		date started
2		length	15 pages
3		things to try	p10-11
4		things to try	p13
5	chapter 2
		word count
		date started
6		length	5 pages
7		things to try	p16
8	chapter 3
		word count
		date started
9		length	15 pages
10	chapter 4
		word count
		date started
11		length	20 pages
12		things to try	p43

Thank you so much for your help!

Regards,

Kang

[#2]

@khjhzw 

Run this macro:

Sub InsertRows()
    Dim rng As Range
    Dim r As Long
    Application.ScreenUpdating = False
    Set rng = Range("B:B").Find(What:="chapter*", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not rng Is Nothing Then
        Do
            r = rng.Row
            rng.Offset(1).Resize(2).EntireRow.Insert
            rng.Offset(1, 1).Value = "word count"
            rng.Offset(2, 1).Value = "date started"
            Set rng = Range("B:B").Find(What:="chapter*", After:=rng, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Row > r
    End If
    Application.ScreenUpdating = True
End Sub

[#3 · Accepted solution]

@khjhzw 

Sub insertrow()

Dim i As Integer
Dim j As Integer

For i = 1 To 1000

j = InStr(1, Cells(i, 1), "chapter", vbTextCompare)

If j = 1 Then
Cells(i + 1, 1).EntireRow.Insert
Cells(i + 2, 1).EntireRow.Insert

Cells(i + 1, 2).Value = "word count"
Cells(i + 2, 2).Value = "date started"

i = i + 2

Else
End If

Next i

End Sub

Maybe with these lines of code. Click the button in cell E1 in the attached file to start the macro.

[#4]

Thank you so much for your help. It works! Fabulous. Wonderful!

Regards,
Kang

[#5]

Dear Hans Vogelaar,

Thank you so much for your timely reply and helping me out for the second time. I really appreciate your help.

Regretfully, your macro didn't work. However, macro suggested by another person, "Quadruple_Pawn", did the job. I wish I could tell you why your macro didn't work, but I know nothing about excel macro. Please pardon me. Thank you anyway for your input!

Regards,
Kang

[#6]

@OliverScheurich 

Similar to what I need. 

https://techcommunity.microsoft.com/t5/excel/how-to-insert-or-create-new-rows-under-every-row-that-m...
 I have an excel 2019  form with 10 rows and columns A-Z (26).  Some files have less than 10 entries. Some files have more than 10 entries.  All cells have a formula with in them.

Column C is always an integer, Column D are Alphanumeric, Column G is  Alphanumeric text. If that matters.

  I do not know VBA but I Need a simple macro to add another row (row 11) when Cells 10C and 10D and 10G all have data. So by the time I fill in 10Z there is a row 11 and the cells in row 11 have a incremented formula.

This repeats so I will always have one more line than I need.

Basically if the last line starts gets three trigger cells, filled with any data, Add another line, filled with increment formulas based on the previous formulas above each respective cell.

Rows also have formatted boarders. New cells in the new row need the same format boarder as the row above.

Form will have a minimum of fixed 10 rows.

Could have up to another 100 conditional rows. 

This would save on paper when the list is printed.

[#7]

@OliverScheurich Similar to what I need. https://techcommunity.microsoft.com/t5/excel/how-to-insert-or-create-new-rows-under-every-row-that-m... I have an excel 2019 form with 10 static data entry rows and columns A-AD (30). Some files have less than 10 entries. Some files have more than 10 entries. All cells have a formula with in them. Manually inserting new rows is slow. Rows 11-25 do various calculations on the inserted datas. Column C is always an integer, Column D are Alphanumeric, Column G is Alphanumeric text. If that matters. I do not know VBA or macros. but I know need a simple (background) macro to add/insert another row (row 11) when Cells 10C and 10D and 10G all have data. So by the time I fill in cell 10Z there is a row 11 and the cells in row 11 have a incremented formula. When Cells 11C and 11D and 11G all have data a row 12 is created. This repeats so I will always have one more line than I need. Basically if the last line starts gets three trigger cells, filled with any data, Add another line, filled with increment formulas based on the previous formulas above each respective cell. Rows also have formatted boarders. New cells in the new row need the same format boarder as the row above. Form will have a minimum of fixed 10 rows. Could have up to another 100 conditional rows of variable length This would save on paper when the list is printed. Need help please. MRGCAV

[#8]

@khjhzw 

Similar to what I need.
I have a form with 10 rows and columns A-Z (26).  Some files have less than 10 entries. Some files have more than 10 entries.  All cells have a formula with in them.

I do not know VBA but I Need a simple macro to add another row (row 11)when Cells 10A and 10B and 10C all have data. so by the time I fill in 10Z there is a row 11 and the cells in row 11 have a incremented formula.

w

[#9]

If you create a new thread and upload your file for a new question,I guess new discussion can start soon.