Inserting a row inbetween consecutive conditions - Need Help VBA

C

CoraG

New Member
Joined
Aug 30, 2018
Messages
31
I am having difficulty a code that provides for inserting a row when there are to FALSE that fall consecutively. I would like to insert a row when this shows up. Do I use AND IF statement? The True/False column is M on my spreadsheet.

Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row
For i = Lastrow To 2 Step by - 1

If Worksheets("APR ADJ").Cells(i, 12).Value = "FALSE FALSE" Then
ActiveCell.EntireRow.Insert Shift:=xlDown
End If
Next
Worksheets("APR ADJ").Cells(1, 1).Select
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Welcome to the Board!

Don't use ActiveCell, as that is the cell that is active when your code starts, and you are selecting any cells in your code.
Try something like this:
Code: 
[COLOR=#333333]Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row[/COLOR]
[COLOR=#333333]For i = Lastrow To 2 Step by - 1[/COLOR]
[COLOR=#333333]    If Worksheets("APR ADJ").Cells(i, 12).Value = "FALSE FALSE" Then[/COLOR]
[COLOR=#333333]        Rows(i).Insert Shift:=xlDown[/COLOR]
[COLOR=#333333]    End If[/COLOR]
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]Worksheets("APR ADJ").Cells(1, 1).Select[/COLOR]

If you want it to insert the row below the row instead of above it, change the Insert line to:
Code: 
[COLOR=#333333]       Rows(i+1).Insert Shift:=xlDown[/COLOR]
 
Last edited:
Upvote 0
It didn't do anything... not sure why.... This part of the data set..
[TABLE="width: 859"]
<colgroup><col span="8"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]BNR[/TD]
[TD]ORG_NAME[/TD]
[TD]PROJECT_NUMBER[/TD]
[TD]TASK_NUMBER[/TD]
[TD]EXP_ITEM_DATE[/TD]
[TD]EXP_END_DATE[/TD]
[TD]HOURS[/TD]
[TD]SLR_RATE[/TD]
[TD]LABOR_COST[/TD]
[TD]1,2,3[/TD]
[/TR]
[TR]
[TD="align: right"]2456[/TD]
[TD]Robert[/TD]
[TD]CC From[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD="align: right"]2/6/2018[/TD]
[TD="align: right"]2/11/2018[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]96.14[/TD]
[TD] $ (865.26)[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]2456[/TD]
[TD]Robert[/TD]
[TD]CC To[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD="align: right"]2/6/2018[/TD]
[TD="align: right"]2/11/2018[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]96.14[/TD]
[TD] $ 192.28[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]2456
[/TD]
[TD]Robert[/TD]
[TD]CC To[/TD]
[TD]MB0104021[/TD]
[TD]3S000A[/TD]
[TD]JW8S00[/TD]
[TD]QUALPLCH[/TD]
[TD="align: right"]2/6/2018[/TD]
[TD="align: right"]2/11/2018[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]96.14[/TD]
[TD] $ 672.98[/TD]
[TD="align: center"]FALSE


[/TD]
[/TR]
</tbody>[/TABLE]
See how the there are consecutive False... I need to insert a row between the two False transactions which are on column M

So every time there are False transactions one after the other is where I need the row inserted. I hope this helps
 
Upvote 0
I thought your code looked a little funny.

A few problems with your code:
- Column "M" is column 13, not column 12 (note: in Cells(row,column), you can use the column number or letter)
- You want to check for FALSE on two separate rows. Your code currently looks for two FALSE entries in the same cell
- It should be "Step -1", not "Step by -1"
- FALSE and "FALSE" are not the same thing. One is a boolean value (FALSE) and one is literal text ("FALSE"). I am not sure which one you have.

Here is updated code that should do what you want. If it does not do anything, try changing FALSE to "FALSE" in the code.
Code: 
Sub MyInsertRows()

    Dim Lastrow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row
    For i = (Lastrow - 1) To 2 Step -1
        If Worksheets("APR ADJ").Cells(i, "M") = False And _
            Worksheets("APR ADJ").Cells(i + 1, "M") = False Then
            Worksheets("APR ADJ").Rows(i + 1).Insert Shift:=xlDown
        End If
    Next i

    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
YOU ROCK!!!!

This really worked... So I caught the error as in the column 12. I am assuming my FALSE are boolean value since I didn't do a copy paste special value to change it to text. The other question I have is if you notice in column 3 CC From, CC To, CC To. The reason I needed to insert a row is to add the column to offset the last CC To. What I need to do is copy the From Data line up to the 9th column. How do you recommend I do this. Boy have you made life simpler for me... I am talk about thousands of rows of data I am having to do this with. Let me know thoughts. Thank you!
 
Upvote 0
What I need to do is copy the From Data line up to the 9th column.
Click to expand...
OK, so in the simple example you posted, we are inserting a blank line under row 3 to separate the two FALSE rows (when looking at column "M").
It sounds like you want to copy column A-I to this new blank row, but I am unclear if we are copying from the row above the inserted row we just created, or from the just below the insert row.
Can you clarify that?

And do we really need column C to say "CC From", not "CC To" (which is what we would get if we were copying from the adjacent row)?
 
Upvote 0
Okay, so let me clarify. The data needs to be in From To format for every row. If you look at the first data you helped me create a row so I can copy the data from the CC From. What I need is to be able to copy the data up to EXP_END_DATE (Column 9) like so....

(From/To/To) - [TABLE="width: 867"]
<tbody>[TR]
[TD="colspan: 2"]FIRST DATA SET
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]BNR[/TD]
[TD]ORG_NAME[/TD]
[TD]PROJECT_NUMBER[/TD]
[TD]TASK_NUMBER[/TD]
[TD]EXP_ITEM_DATE[/TD]
[TD]EXP_END_DATE[/TD]
[TD]HOURS[/TD]
[TD]SLR_RATE[/TD]
[TD]LABOR_COST[/TD]
[TD]1,2,3[/TD]
[/TR]
[TR]
[TD]2456[/TD]
[TD]Robert[/TD]
[TD]CC From[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD]-9[/TD]
[TD]96.14[/TD]
[TD="align: right"]($865.26)[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2456[/TD]
[TD]Robert[/TD]
[TD]CC To[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD]2[/TD]
[TD]96.14[/TD]
[TD="align: right"]$192.28[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2456[/TD]
[TD]Robert[/TD]
[TD]CC From[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2456[/TD]
[TD]Robert[/TD]
[TD]CC To[/TD]
[TD]MB0104021[/TD]
[TD]3S000A[/TD]
[TD]JW8S00[/TD]
[TD]QUALPLCH[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD]7[/TD]
[TD]96.14[/TD]
[TD="align: right"]$672.98[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
The data set may also look like this
(From/From/To) - Which in this case I would need to copy the CC To column. The first code helped me separate all the consecutive FALSE transaction where either of these type of situations occur in the data set.
[TABLE="width: 867"]
<colgroup><col span="5"><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]SECOND DATA SET[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]BNR[/TD]
[TD]ORG_NAME[/TD]
[TD]PROJECT_NUMBER[/TD]
[TD]TASK_NUMBER[/TD]
[TD]EXP_ITEM_DATE[/TD]
[TD]EXP_END_DATE[/TD]
[TD]HOURS[/TD]
[TD]SLR_RATE[/TD]
[TD]LABOR_COST[/TD]
[TD]1,2,3[/TD]
[/TR]
[TR]
[TD]3256[/TD]
[TD]Sam[/TD]
[TD]CC From[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD]-9[/TD]
[TD]96.14[/TD]
[TD="align: right"]($865.26)[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]3256[/TD]
[TD]Sam[/TD]
[TD]CC From[/TD]
[TD]NN9100000[/TD]
[TD]3S000A[/TD]
[TD]H9BA00[/TD]
[TD]FORM0100[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD]-2[/TD]
[TD]96.14[/TD]
[TD="align: right"]$192.28[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2456[/TD]
[TD]Sam[/TD]
[TD]CC To[/TD]
[TD]MB0104021[/TD]
[TD]3S000A[/TD]
[TD]JW8S00[/TD]
[TD]QUALPLCH[/TD]
[TD]2/6/2018[/TD]
[TD]2/11/2018[/TD]
[TD]7[/TD]
[TD]96.14[/TD]
[TD="align: right"]$672.98[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

So, the end state is to copy the CC From where needed or the CC To where needed up to Column 9. I hope this makes sense now.
 
Upvote 0
Correction - "Which in this case I would need to copy the CC To row up to column 9." Sorry about that
 
Upvote 0
I am not sure that you answered the question I was after, and that is which row is it copying from.
But in your first example, it looks like the blank row that is being inserted is copying from the row just above it. So I am going to proceed under that assumption.
Code: 
Sub MyInsertRows()

    Dim Lastrow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Lastrow = Worksheets("APR ADJ").Cells(Rows.Count, 12).End(xlUp).Row
    For i = (Lastrow - 1) To 2 Step -1
        If Worksheets("APR ADJ").Cells(i, "M") = False And _
            Worksheets("APR ADJ").Cells(i + 1, "M") = False Then
            Worksheets("APR ADJ").Rows(i + 1).Insert Shift:=xlDown
'           Copy columns 1-9 from row above
            Worksheets("APR ADJ").Range(Cells(i, "A"), Cells(i, "I")).Copy _
                Worksheets("APR ADJ").Cells(i + 1, "A")
'           Update value in column C
            If Worksheets("APR ADJ").Cells(i + 1, "C") = "CC From" Then
                Worksheets("APR ADJ").Cells(i + 1, "C") = "CC To"
            Else
                Worksheets("APR ADJ").Cells(i + 1, "C") = "CC From"
            End If
        End If
    Next i

    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Hi thank you. So, the reason I didn't give you a row is because there can be one CC From and 5 CC To or 5 CC From(s) to one or several CC To(s)ice versa so it isn't based on row above or below. Is there a way to identify the row to copy from based on that? So, column m identifies all those transactions that are consecutive CC From(s) or CC To(s). Based on Column 8 date.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
198
who am i
W
O
  • Solved
VBA - floating photo
Replies
3
Views
341
RoryA
RoryA
M
  • Solved
Insert new row with incremental formula
Replies
8
Views
584
Sheik
S
K
  • Question Question
Help Needed
2
Replies
13
Views
319
kwavic
K
I
  • Solved
Insert new row happening on incorrect sheet
Replies
3
Views
151
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top