Excel Deleting rows with quantity only zero

I am trying to delete Rows have only a quantity of "0" using VBA but the code that i am using is deleting everything that has a "0" in the quantity. like 600 will be deleted because it has a "0"

This the code below.

Sub DeleteRows()
    Dim c As Range
    Dim SrchRng

    Set SrchRng = ActiveSheet.Range("G1", ActiveSheet.Range("G65536").End(xlUp))
    Do
        Set c = SrchRng.Find("0", LookIn:=x1Values)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
End Sub

Answers


Change this:

Set c = SrchRng.Find("0", LookIn:=x1Values)

To this (add the LookAt argument and correct the typo in the LookIn argument):

Set c = SrchRng.Find("0", LookIn:=xlValues, LookAt:=xlWhole)

Documentation for the Range.Find method, provides some information about the optional arguments.

However, I think the range.find method is expensive. A simple iteration might be faster:

Sub DeleteRows()
    Dim c As Range
    Dim i as Long
    Dim SrchRng

    Set SrchRng = ActiveSheet.Range("G1", ActiveSheet.Range("G65536").End(xlUp))
    Application.ScreenUpdating = False
    For i = SrchRng.Cells.Count to 1 Step -1
        Set c = SrchRng.Cells(i)
        If c.Value = "0" Then c.EntireRow.Delete
    Next
    Application.ScreenUpdating = True
End Sub

You need to add an option:

Set c = SrchRng.Find("0", LookIn:=xlValues, LookAt:=xlWhole)

To look at the "whole" cell


If you have a large data set this is fast

Sub DeleteRows()

Application.DisplayAlerts = False

Dim ws As Excel.Worksheet
Dim lCol As Long
Dim lRow As Long

Set ws = ThisWorkbook.Sheets("mySheetName")

 lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
 lRow = ws.Range("G" & ws.Rows.Count).End(xlUp).Row

With ws
  .AutoFilterMode = False
  .Range("A1", Cells(lRow, lCol)).Cells.AutoFilter
  .Range("A1", Cells(lRow, lCol)).Cells.AutoFilter Field:=7, Criteria1:="0"
  .UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
  .AutoFilterMode = False
  .Range("A1").End(xlDown).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count).EntireRow.Delete
End With

Application.DisplayAlerts = True

End Sub

Need Your Help

How to add shebang #! with php script on linux?

php linux shebang

I'm having a little issue with adding shebang #! with my php script on RedHat linux. I have a small piece of test code with shebang added (I've tried different variations as well), but I get the

How to get web camera fps rate in OpenCV?

c++ c video opencv webcam

So I need to get web camera fps rate in OpenCV. Which function can do such thing for?