Macro to insert row above depending on a certain cell value

[Tigran]

Hello,

I am really new to VBA/Macro, and this is my first question here, so please bear with me.

I am trying to write a macro for the following situation, but so far I have not had any luck.
I am using Excel 2019.
Below is a simplified view of my excel sheet that in reality has more than 100k rows like this:

A	B	C	D
2551		2551	true
2552		2552	true
2554		2555	false
2555		2557	false

Column A is one table, columns C and D - another.
Column D contains an IF formula to compare if columns A and C are equal and show true or false.
I am keeping two sets of data in different table to auto-update the formula results after any rows changes.
What I need is a macro to:
1. check column D from up to down (since whatever VBA I have found in forums check from down up),
2. find the false result (while the value is in a formula),
3. insert a row above the row with the false value,
4. after this happens, the table will have the following view:

A	B	C	D
2551		2551	true
2552		2552	true
2554			false
2555		2555	true
		2557	false

5. the row with 2555 values will become "true", and I want the macro to go down (not start looking from 1st row anew), find the next "false", insert row and same way down to the last row of the sheet.

Is such a Macro/VBA possible?
Thanks a bunch in advance, this will save me tons of time.

 

[Zot]

When you insert a blank row in table CD, the last row between table A and table CD would be different. When macro should stop execution? End of data in table A?

 

[Tigran]

When you insert a blank row in table CD, the last row between table A and table CD would be different. When macro should stop execution? End of data in table A?

My mistake for not clarifying this.
Column B is jut a blank column between Tables A and CD, to show the cutting line between Tables including columns A on one side and CD on the other. Macro should stop execution when the Table ends with "true" value in Column D (by table I mean when a section of the Excel behaves as separate entity, with its unique color scheme (as I mentioned, I am really new to this, ask me if something is not clear)). Or, if it's not possible, we can set it to finish when we get "true" all the way down to the worksheet limit.

 

[Tigran]

And I just realized what you meant. The end of data in table A will actually be the best finishing line for the VBA.

 

[Zot]

And I just realized what you meant. The end of data in table A will actually be the best finishing line for the VBA.

In this macro, actually the equation in column D is copied from the row above. So I guess you just need the formula in range D2

Sub InsertOnFalse()

Dim n As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

Application.ScreenUpdating = False

n = 2
Do While Not ws.Range("A" & n) = ""
If ws.Range("D" & n) = False Then
ws.Range("C" & n, "D" & n).Insert Shift:=xlShiftDown
ws.Range("D" & n).Offset(-1, 0).Copy
ws.Range("D" & n).PasteSpecial (xlPasteFormulas)
End If
ws.Range("D" & n).Copy
ws.Range("D" & n).Offset(1, 0).PasteSpecial (xlPasteFormulas)
n = n + 1
Loop

End Sub

 

[Tigran]

I will try this in the morning (in my GMT), and you have a donation comimg if this works.

 

[Zot]

I will try this in the morning (in my GMT), and you have a donation comimg if this works.

Opps I did not quote the code properly

Sub InsertOnFalse()

Dim n As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

Application.ScreenUpdating = False

n = 2
Do While Not ws.Range("A" & n) = ""
    If ws.Range("D" & n) = False Then
        ws.Range("C" & n, "D" & n).Insert Shift:=xlShiftDown
        ws.Range("D" & n).Offset(-1, 0).Copy
        ws.Range("D" & n).PasteSpecial (xlPasteFormulas)
    End If
    ws.Range("D" & n).Copy
    ws.Range("D" & n).Offset(1, 0).PasteSpecial (xlPasteFormulas)
    n = n + 1
Loop

End Sub

 

[Tigran]

Opps I did not quote the code properly

Sub InsertOnFalse()

Dim n As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

Application.ScreenUpdating = False

n = 2
Do While Not ws.Range("A" & n) = ""
    If ws.Range("D" & n) = False Then
        ws.Range("C" & n, "D" & n).Insert Shift:=xlShiftDown
        ws.Range("D" & n).Offset(-1, 0).Copy
        ws.Range("D" & n).PasteSpecial (xlPasteFormulas)
    End If
    ws.Range("D" & n).Copy
    ws.Range("D" & n).Offset(1, 0).PasteSpecial (xlPasteFormulas)
    n = n + 1
Loop

End Sub

Hi, thanks for this.
When I executed the VBA, the only thing that happens is that the last cell after which the value changes is selected.
Should I specify a value in this argument?:
Do While Not ws.Range("A" & n) = ""

Whenever I specify "true" or "false", the Excel goes into a very long loading and I have to terminate Excel processes in Task Manager.
This happens even if I do it on an Excel sheet with only 10 rows.

Please help.
Thanks in advance.

T.

 

[Zot]

Hi, thanks for this.
When I executed the VBA, the only thing that happens is that the last cell after which the value changes is selected.
Should I specify a value in this argument?:
Do While Not ws.Range("A" & n) = ""

Whenever I specify "true" or "false", the Excel goes into a very long loading and I have to terminate Excel processes in Task Manager.
This happens even if I do it on an Excel sheet with only 10 rows.

Please help.
Thanks in advance.

T.

Maybe I misunderstood something here. Since you mentioned 2. find the false result (while the value is in a formula), I imagined that your sheet is like this

Book1
	A	B	C	D
1	A	B	C	D
2	2551		2551	TRUE
3	2552		2552	TRUE
4	2554		2555	FALSE
5	2555		2557	FALSE
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=A2=C2

When macro run, it was looking for the last line in column A (no more data). That is why
Do While Not ws.Range("A" & n) = ""
meaning keep looping until range A is blank
Running code for the table above resulting this

Book1
	A	B	C	D
1	A	B	C	D
2	2551		2551	TRUE
3	2552		2552	TRUE
4	2554			FALSE
5	2555		2555	TRUE
6			2557	FALSE
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=A2=C2

I do not understand your statement here. You were seeing other things?
Whenever I specify "true" or "false", the Excel goes into a very long loading and I have to terminate Excel processes in Task Manager.
This happens even if I do it on an Excel sheet with only 10 rows.

 

[Tigran]

Maybe I misunderstood something here. Since you mentioned 2. find the false result (while the value is in a formula), I imagined that your sheet is like this

Book1
	A	B	C	D
1	A	B	C	D
2	2551		2551	TRUE
3	2552		2552	TRUE
4	2554		2555	FALSE
5	2555		2557	FALSE
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=A2=C2

When macro run, it was looking for the last line in column A (no more data). That is why
Do While Not ws.Range("A" & n) = ""
meaning keep looping until range A is blank
Running code for the table above resulting this

Book1
	A	B	C	D
1	A	B	C	D
2	2551		2551	TRUE
3	2552		2552	TRUE
4	2554			FALSE
5	2555		2555	TRUE
6			2557	FALSE
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=A2=C2

I do not understand your statement here. You were seeing other things?
Whenever I specify "true" or "false", the Excel goes into a very long loading and I have to terminate Excel processes in Task Manager.
This happens even if I do it on an Excel sheet with only 10 rows.

OMG, sorry, I was doing something wrong the first time, this works perfectly.
I'd like to make a donation, as you really helped me a lot. I don't know if there is PM feature in this forum, but please let me know your PayPal ID somehow.
Thanks again!

T.