Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

Excel VBA: dynamically insert blank rows after a row with a call containing a ceritan value

Need to insert a blank row after the row containting "Non Target" (Column C).  The number of times the value "Non Target" cna appear will vary, so it should be dynamic.

Thanks
ee-format-samp.xls
0
LeeStink
Asked:
LeeStink
  • 3
2 Solutions
 
Saurabh Singh TeotiaCommented:
Use this code and it will do what you are looking for...
Saurabh...

Sub insertrows()
    Application.ScreenUpdating = False
 
    Dim i As Long
    i = 5
    Do Until i > Cells(65536, "C").End(xlUp).Row
        If Cells(i, "C").Value = "Non-Target" Then
            Rows(i + 1).Insert
            i = i + 2
        Else
            i = i + 1
        End If
    Loop
 
    MsgBox "Done"
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
DaveCommented:
Using Find (as below) or Filter will offer the quickest code fix
Instructions to run thge macro included in the code below
Regards
Dave

'Press Alt + F11 to open the Visual Basic Editor (VBE)
'From the Menu, choose Insert-Module.
'Paste the code into the right-hand code window.
'Press Alt + F11 to close the VBE
'Go to Tools & Macro ... Macros and double-click InsRow
 
Sub InsRow()
    Dim rng1 As Range, rng2 As Range, cel As Range
    Dim FirstAddress As String
    Dim AppCalc As Long
 
    Application.ScreenUpdating = False
 
    Set cel = Columns("C").Find("Non-Target", , xlValues, xlWhole, xlByRows, , False)
    If Not cel Is Nothing Then
        Set rng2 = cel
        FirstAddress = cel.Address
        Do
            Set cel = Columns("C").FindNext(cel)
            Set rng2 = Union(rng2, cel)
        Loop While FirstAddress <> cel.Address
    End If
 
    If Not rng2 Is Nothing Then rng2.EntireRow.Offset(1, 0).Insert
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
lethalzCommented:
Hi LeeStink,

Try this code.
Sub InsertBlankRow()
    i = 6
    With ActiveSheet
        Do While .Cells(i, 3) <> ""
            If .Cells(i, 3) = "Non-Target" Then
                Rows(i + 1 & ":" & i + 1).Insert Shift:=xlDown
                i = i + 1
            End If
            i = i + 1
        Loop
    End With
    Range("A6").Select
End Sub

Open in new window

0
 
DaveCommented:
Any update here LeeStink?
Cheers
Dave
0
 
DaveCommented:
Recommend a split between http:#a24134040 and http:#a24133143 to recognise the first solution provided by Saurabh and the different approach by me which will cut run time

Regards

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now