Macro to find zero values in a certain column and delete tha
Find bottlenecks in your Excel workbooks
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

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
  •  

 

 
DMCA.com