Insert X number of rows and insert formula in that rows

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
good mornig, I burning my brain for two days with (i guess it is) a simple macro
I have a sheet with X number of rows, what I need is insert a new row above each existing row, wich I already did, now i need to put a formula in each new row.

imagine this: an array with 20x20 with 0 and 1,

each row have a X number of columns filled, wich dificults a little, because I don't want a formula with a blank cell bellow.
The formula can be anything, "=IF(a2="0";"zero";"one") for example.

VBA Code:
Sub InserLine()
   Dim i As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Cells(i, 1) <> Cells(i - 1, 1) Then Rows(i).Resize(1).Insert
   Next i
End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
forgot to say, the only condition for the formula is that it uses the cell bellow as reference

as:
cell value
A1 = Formula (IF A2<>"";"ok";"not ok")
A2 = 1
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,528
Members
410,845
Latest member
OldSwimmer1650
Top