looking for a sort and delete macro

[JasonLeVan]

in column f I have a formula that computes price changes. if it goes up say .20 cents it say ".20" if down then"-.20". works great. but the report is forever long. want i want to do is make a macro that will go thru and look at column f, if it went up or down more than a dollar then keep it on the report but if not to delete the whole row.

 

[JackDanIce]

Try:

Sub RemoveLessThan0 ()
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Dim i As Long
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Abs(Range("F" & i)) < 1 Then Range("F" & i) = 0
Next i
Range("A1").CurrentRegion.AutoFilter field:=6, Criteria1:="0"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
With Application
   .ScreenUpdating = True
   .DisplayAlerts = True
End With
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
End Sub

 

[JackDanIce]

Looking for a sort and delete macro

Actually, I prefer this code:

Sub CombineData()
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Dim i As Long
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Abs(Range("F" & i)) < 1 Then Range("F" & i) = 0
Next i
Range("A1").CurrentRegion.AutoFilter field:=6, Criteria1:="0"
Application.DisplayAlerts = False
On Error Resume Next
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1) _
    .SpecialCells(xlCellTypeVisible).Rows.Delete
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[JasonLeVan]

Re: Looking for a sort and delete macro

I tried both and get the same error on both at line

If Abs(Range("F" & i)) < 1 Then Range("F" & i) = 0
not sure what it is telling me, just says type mismatch

 

[JackDanIce]

That line assumes the values in column F are numerical and is testing them by saying:

If the absolute value of the cell is less than 1, Then change that value to 0

The macro then deletes any rows containing a value of 0, I'm guessing the values in your column F are not numerical

Changing the values in column F so they are numerical or changing the code to:

If Abs(Range("F" & i)+0) < 1 Then Range("F" & i) = 0