frhling
12-19-2012, 03:48 AM
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
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