Deleting Rows with Certain Values in VBA

[tanney9]

I'm running into an issue when I try to generate a report for the largest five values in a report. Some of the cells have a zero divisor in their formula so they come up with the #DIV/0 error. I've put in an If statement to make those equal 0, but the LARGE formula still returns #NAME?. Any ideas on how to get the LARGE formula to work.

I would like to be able to delete the rows with zero in the reference column, so their data is not even in the report. Does anyone know how to do this in VBA?

 

[VoG]

Hello and welcome to MrExcel.

Which is the reference column?

 

[tanney9]

=IF(D65=0,0,(K65/J65)*10000), where J65 has the zero value in it

 

[VoG]

Try this to delete rows where J is 0

Sub DelJ0()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("J" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("J" & i)
        If .Value = 0 Then .EntireRow.Delete
    End With
Next i
Application.ScreenUpdating = True
End Sub

 

[tanney9]

That worked great. Thanks

 

[tanney9]

Maybe someone can help with this again. I've copied the command above to another macro. This time instead on looking at column J, I want to delete if column E is zero. I made the changes from J to E. But I'm getting a Compile error: Expected End Sub.

Prior to this in the macro. I'm copying and pasting the values to get rid of formulas. Any help would be greatly appreciated.

 

[VoG]

Can you post your complete code.

 

[tanney9]

Sub TESE_B35()
'
' TESE_B35 Macro
' Macro recorded 7/1/2009 by Michael Smith
'

'
Sheets("Chase").Select
Application.Run "BLPLinkReset"
Rows("6:6").Select
Selection.Copy
Rows("7:7").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Sub DelE0()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
With Range("E" & i)
If .Value = 0 Then .EntireRow.Delete
End With
Next i
Application.ScreenUpdating = True




Dim filename As String
filename = "T:\QSG\TESE\Trade Reports\TESEChase_ExecutionReport_" + Format(Now(), "yyyymmdd")
ActiveWorkbook.SaveAs filename, xlNormal

End Sub

 

[VoG]

You need to delete this line from the middle of the code

Sub DelE0()

 

[tanney9]

Error went away, but did not delete any of the rows.

Check that I made an input error. Thanks for the help AGAIN.