Help with IF statement

[mp7]

Hi there,

I am a total novice at excel and wanted to get some advice/help.

Basically, I have a 4 columns of data with about 75 rows. In column D, the values vary from 0 to 500000. I want to create a macro/code which will do the following:

1. Sort the data in column C (not D) by size with largest at the top.
2. I want to then work my way up from the bottom so that the number in the last cell is compared with the one above it. If the one above is smaller it is kept but if it is bigger, the whole row will be deleted. This process should be repeated for each cell until the values in column D decrease from largest at the bottom to smallest at the top.
3. I want to then subtract the top cell (not including the heading) from the one below it - ie c2-c3, then c3-c5, etc. and place the result in column E.
4. I want to then subtract D3-2, D4-D3 etc and place the result in column F.
5. I want to divide column F by column E and place the result in column G

I am not sure if this is clear or possible. Any help would be appreciated.

Thanks.

 

[stnkynts]

I think i got it here. I assumed that there is a header in row 1. Also this macro has Sheet 1 named as the default Sheet1. If your sheet 1 is named something else just change it in the parenthesis of the code line:

Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")

Here is whole code:

Option Explicit
Sub MainMacro()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim lastrow As Long, icell As Long
lastrow = ws1.Range("D" & Rows.Count).End(xlUp).Row
'sorting
With ws1.Sort
        .SortFields.Add Key:=Range("C2:C" & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlDescending
        .SetRange Range("A1:D" & lastrow)
        .Orientation = xlTopToBottom
        .Apply
End With
'sorting/deleting rows in column D
For icell = lastrow To 3 Step -1
Repeat:
    If ws1.Range("D" & icell).Offset(-1, 0).Value > ws1.Range("D" & icell).Value Then
        ws1.Range("D" & icell).Offset(-1, 0).EntireRow.Delete Shift:=xlUp
        GoTo Repeat
    End If
Next icell
'Formulas
lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E3").Formula = "=C2 - C3"
Range("E3:E" & lastrow).FillDown
Range("F3").Formula = "=D3 - D2"
Range("F3:F" & lastrow).FillDown
Range("G3").Formula = "=F3 / E3"
Range("G3:G" & lastrow).FillDown
End Sub

 

[mp7]

Thanks for the help. I ran the code you posted but it seems to work its way up the numbers but seems to eventually delete all the values.

Should I post some examples of the spreadsheet.

 

[stnkynts]

Yes. If you could post some examples it would be benefitial.