Macro to insert row above depending on a certain cell value

Tigran

New Member
Joined
Mar 30, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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:

ABCD
25512551true
25522552true
25542555false
25552557false

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:

ABCD
25512551true
25522552true
2554false
25552555true
2557false

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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,140
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,140
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 30, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
I will try this in the morning (in my GMT), and you have a donation comimg if this works.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,140
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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
 
Solution

Tigran

New Member
Joined
Mar 30, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Opps I did not quote the code properly

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,140
Office Version
  1. 2016
Platform
  1. Windows
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
ABCD
1ABCD
225512551TRUE
325522552TRUE
425542555FALSE
525552557FALSE
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=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
ABCD
1ABCD
225512551TRUE
325522552TRUE
42554FALSE
525552555TRUE
62557FALSE
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=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

New Member
Joined
Mar 30, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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
ABCD
1ABCD
225512551TRUE
325522552TRUE
425542555FALSE
525552557FALSE
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=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
ABCD
1ABCD
225512551TRUE
325522552TRUE
42554FALSE
525552555TRUE
62557FALSE
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,380
Messages
5,635,918
Members
416,887
Latest member
SheriE

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
Top