Forum Discussion

Copper Contributor

Mar 10, 2022

Solved

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

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 w...

Mar 10, 2022

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.

rows.xlsm15 KB

HansVogelaar

MVP

Mar 10, 2022

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

khjhzw
Copper Contributor
Mar 10, 2022
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
- mrgcav
  Copper Contributor
  Oct 20, 2023
  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
  - peiyezhu
    Bronze Contributor
    Oct 21, 2023
    If you create a new thread and upload your file for a new question,I guess new discussion can start soon.