Changing values in a column to zero, then back again

[graemeal]

I have 2 columns shown below, column C and column D. Both columns have formulas in them already. I now sort column D from largest to smallest by a macro.

I would like create a condition in another macro that changes the value in column D to 0 if the value opposite in C column is less than 0, then sort from largest to smallest.

C D
%%% Gain
2 -1.34 2.30
3 3.61 2.15
4 -3.72 1.67
5 -4.05 1.50
6 0.76 1.20
7 5.60 1.10

So the macro would change D2, D4, and D5 to zero then Sort D from largest to smallest would produce the bottom example.

C D
%%% Gain
2 3.61 2.15
3 0.76 1.20
4 5.60 1.10

Ideally I would like to reverse that via another Macro.

 

[StephenCrump]

Rather than changing formulae to zero values, and having to reinstate the formulae, why not simply filter?

With ActiveSheet
    If .AutoFilterMode Then
        .AutoFilterMode = False
    Else
        .Rows("1:1").AutoFilter Field:=3, Criteria1:=">=0"
    End If
End With

 

[graemeal]

Rather than changing formulae to zero values, and having to reinstate the formulae, why not simply filter?

With ActiveSheet
    If .AutoFilterMode Then
        .AutoFilterMode = False
    Else
        .Rows("1:1").AutoFilter Field:=3, Criteria1:=">=0"
    End If
End With

Where do I insert that code?

I am not very experience in Excel.

 

[graemeal]

I am sorting the D column which is based on values in the C column

 

[graemeal]

Thank you very much, all sorted.

 

[StephenCrump]

You can put the code in its own Sub, and call the Sub any way you like (click a button, click a shape, keyboard shortcut - what are you doing with your current macros?)

Sub ShowHide()

    With ActiveSheet
        If .AutoFilterMode Then
            .AutoFilterMode = False
        Else
            .Rows("1:1").AutoFilter Field:=3, Criteria1:=">=0"
        End If
    End With
       
End Sub

My code doesn't sort column D - you said you do this via another macro, and filtering out the negative column C values won't change the sort order.