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

[Guest]

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

 

[AJ]

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

 

[Marc]

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

Sub DeleteCells4()
'modified from http://support.microsoft.com/support/kb/articles/Q213/5/44.asp
'see http://www.geocities.com/davemcritchie/excel/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

 

[davers5]

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