Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Macro to find zero values in a certain column and delete tha

  1. #1
    Guest

    Default

    Is it possible to create a macro to search for zero values in a certain column and delete the row that contains the zero value? Does anyone have the VBA code for this task?

    cthoesen@cort1.com

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have a look at mine and bobeuk's replies to
    "Selecting a Row based on cell value"
    a bit further down the list.

    Swap the formatting stuff (the With..End With) for
    Selection.Delete Shift:=xlUp

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works by selecting the column of interest and then running the macro.

    Sub DeleteCells4()
    'modified from http://support.microsoft.com/support.../Q213/5/44.asp
    'see http://www.geocities.com/davemcritch...l/delempty.htm

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual 'pre XL97 xlManual
    Dim rng As Range, i As Long '// modified

    'Set the range to evaluate to rng. // modified
    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    If rng Is Nothing Then
    MsgBox "nothing in Intersected range to be checked"
    GoTo done
    End If

    'Loop backwards through the rows
    'in the range that you want to evaluate.
    '--- For i = rng.Rows.Count To 1 Step -1 // modified

    For i = rng.Count To 1 Step -1

    'If cell i in the range contains an "0", delete the entire row.
    If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
    Next
    done:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First select the first cell in the column you are looking for at:
    Cells(1,1).Activate

    Have a parameter in your loop so it knows when to stop (you can go until you get to a certain row):
    Do Until ActiveCell.Row > (LAST ROW YOU WANT TO CHECK)

    Check if the active cell=0:
    If ActiveCell.Value=0 Then

    If it does then delete the row:
    ActiveCell.EntireRow.Delete

    If it does not, check the next cell (deleting the row will automatically move on to the next cell):
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop

    That's it.


    Dave

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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