Inserting rows and copying data from above and below

  • Thread starter Thread starter Ferdinand
  • Start date Start date
F

Ferdinand

Hi,

I have a problem with graphing data logged from a vehicle network bus.
On this network identifiers are only flagged when the sensor value has
changed. When you plot this in Excel you get an oblique line where it
is in reality a horizontal line until the next value.

The solution is to insert a new line, copy the next timeflag and the
previous value. This will off course double the information but that
is not a problem. I currently have sensor files of around 1000+ lines.

An example. Lets say I have the following table:
Time Value
0 0
2 1
5 3
8 6

I need the following table to plot it correctly:
Time Value
0 0
2 0
2 1
5 1
5 3
8 3
8 6

To insert lines I have found the following code in this forum:

Sub InsertRow()
Dim irow As Long, i As Long
irow = Cells(Rows.Count, "A").End(xlUp).Row
For i = irow To 2 Step -1
Rows(i + 1).Insert
Next i
End Sub

But how to include the copy from above and below?

Any help is greatly appreciated.

Ferdinand
 
Ferdinand,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub InsertRow2()
Dim irow As Long, i As Long
irow = Cells(Rows.Count, "A").End(xlUp).Row
For i = irow To 2 Step -1
Rows(i + 1).Insert
Next i

Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
Columns("B:B").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Columns("A:B").Copy
Columns("A:B").PasteSpecial Paste:=xlPasteValues
End Sub
 
Bernie,

Thanks for your help. Yes this works fine, neat trick to make formulas
in RC format and inserting in the emptycells! Also the copy-paste to
get the value message back instead of the formulae is good.

Thanks again, Ferdinand
 
Back
Top