Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    39

    Unanswered: Deleting Rows using VB

    Hello!

    Can anyone give me VB code to delete a row if the cell value in a column = "0"? I'm new to the whole VB thing.
    Based on the attached screenshot, I'm trying to remove the rows entirely if they have a zero in column E.

    Much Thanks!

    B
    Attached Thumbnails Attached Thumbnails aaa.PNG  

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. This should meet your needs:

    Code:
    Sub DeleteCriteriaTest()
        Dim n As Long, LastRow As Long
        Dim myCrit As Range
        LastRow = Range("E" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
        For n = LastRow To 2 Step -1
            Set myCrit = Cells(n, 5)
            If myCrit.Value = 0 Then Rows(n).EntireRow.Delete
        Next n
    Application.ScreenUpdating = True
    End Sub
    Last edited by shades; 11-05-07 at 16:02.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2006
    Posts
    39

    Thanks!

    Shades - I appreciate the help.
    Thank you.

  4. #4
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    The solution could also be:

    Sub proDelete()
    Do Until Selection.Value = ""
    If Selection.Value = 0 Then
    Selection.EntireRow.Delete
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop
    End Sub
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    or yet even another way

    Code:
    Option Explicit
    
    Sub runMacro()
        Call DeleteRowswithValue(Cells(1, 1), 0)
    End Sub
    
    Sub DeleteRowswithValue(rngColumn As Range, iValue As Integer)
        Dim rngActive As Range
        Dim bFoundCell As Boolean
        
        'initalise bfoundcell
        bFoundCell = True
        
        Do Until Not bFoundCell
            'attempt to find the value
            Set rngActive = rngColumn.EntireColumn.Find(what:=iValue, lookat:=xlWhole)
            If Not rngActive Is Nothing Then
                rngActive.EntireRow.Delete
            Else
                bFoundCell = False
            End If
        Loop
        
        Set rngfoundcell = Nothing
    End Sub
    btw piere your method will fail if there is a blank value in the cell.
    Also i would advice strongly against using select or selections if at all possible.

    There are loads of ways to do this but shades has given a good answer if there is a limit on the amount of data

    Thanks
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •