Solved How do I insert a line when column has specific data in cell

August 28, 2019 at 13:10:21
Specs: Windows 7
Dont know if this can be done via macro or not for excel.

What im trying to do is where data in column "S" says "Ticket Control No." I need a blank row inserted below.. I need it to look all the way down the workbook and insert as necessary.

What I mean for instance is where S5 says "Ticket Control No." I need a blank row inserted below in row 6 across all columns.

I also need it to look all the way down the workbook and insert as necessary as they will be in different rows although "Ticket Control No." will always be in column S


See More: How do I insert a line when column has specific data in cell

Reply ↓  Report •

#1
August 28, 2019 at 13:31:17
Try this macro:

EDIT: See Response Number 3

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
August 29, 2019 at 10:39:25
Lost code donno whats happened?

message edited by GDB


Reply ↓  Report •

#3
August 29, 2019 at 10:57:41
✔ Best Answer
Hey, sorry, the code from Response #2 may not work in all cases.

That code is going to determine the number of Rows before any Rows are inserted and then only check that number of Rows.

Let's say you have 4 rows with data and they all contain Ticket Control No. lastRw will = 4, so the loop will only check 4 rows.

By the time the code finishes the 4 loops, the original 3rd occurrence of Ticket Control No will have been moved down to S5, which will never be checked.

We need to start at the bottom and check the cells on the way up to eliminate that issue.

My apologies.

Try this:

Sub ControlRow()
Dim lastRw As Long, nxtRw As Long

'Determine last row with data in Column S
   lastRw = Cells(Rows.Count, "S").End(xlUp).Row
   
'Check each Cell in S, Insert row after each Ticket Control No.
    For nxtRw = lastRw To 1 Step -1
      If Cells(nxtRw, "S") = "Ticket Control No." Then
        Rows(nxtRw + 1).EntireRow.Insert
      End If
    Next
    
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
August 29, 2019 at 12:45:04
re: "Lost code donno whats happened?"

I deleted it out because it wasn't "good enough". It was apt to fail in certain condition as described in my Response #3.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question