Solved: substract two adjacent cells, delete every other row

[frhling]

I have an excel sheet and this VBA works for me, but I have few problems:
1- it deletes ColumnD
2- it has a fixed number:159 but my data is not fixed.

the excel sheets are genereted everyday:
3- Is there anyway which it automatically run the program or everytime i should open each excel sheet and run the macro?

I have not written this code and I am NULL in VBA Programming.
is there anyway which it reads till end of rows which has information and then stops and delete rows?

it is clear that it subtracts (B3-B2), (B5-B4),...,(Bn+1 - Bn), writes the output in B2 for example and deletes row 3.

I would appreciated if someone can help me.
Thanks.


Sub Subtraction()
'
' Macro2 Macro
'
Application.ScreenUpdating = False
'
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[1]C[-2]-RC[-2]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D159"), Type:=xlFillDefault
Range("D2:D159").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("D2:D159").Select
Application.CutCopyMode = False
Selection.ClearContents

'DelOddRows()
Dim RowCtr As Double
For RowCtr = 157 To 3 Step -2
Rows(RowCtr).Delete
Next RowCtr
End Sub

[Bob Phillips]

Can you summarise what it should do, it seems to overwrite column D and then paste those values to column B. All very odd.

[frhling]

Can you summarise what it should do, it seems to overwrite column D and then paste those values to column B. All very odd.

Thanks for ur reply. I have explained everything I think.

I need a code to subtract (B3-B2), (B5-B4),...,(Bn+1 - Bn), writes the output in B2 for example and deletes row 3. This code actually does. but as u said deleted Column D & it has a fixed number:159 but my data is not fix.
otherwise it does what I need to do except that everytime I have to change the number and first insert an empty column, then run the macro, then deletes again that coulmn, which is stupid :D

That would be nice if you can help me out.

[Bob Phillips]

Sub Subtraction()
Dim lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = ((lastrow - 1) \ 2) * 2 To 2 Step -2

.Cells(i, "B").Value = .Cells(i + 1, "B").Value - .Cells(i, "B").Value
.Rows(i + 1).Delete
Next i
End With

Application.ScreenUpdating = True
End Sub

[frhling]

Thanks alot.
It is solved.
when I read, I underestand what you have written but I,myself couldnt never write it. Thanks.
but still few small(maybe stupid) questions:
1- what should I do when I want to set it to automatically run the macro.
2- if I find another macro to create line diagram, should I mix these two macros or should be seperated?

[Bob Phillips]

1 - you need to determine first what will trigger the macro

2 - I would create the line chart as is, then add code to the macro to update its source range at the end.

[frhling]

1 - you need to determine first what will trigger the macro

2 - I would create the line chart as is, then add code to the macro to update its source range at the end.

Thanks. I think a little about it, when I really cant do anything, then I post a new thread.

Thanks.