Jul 3, 2013

Remove cells with zero in Excel using VBA

The data is like:

- - - - -
0 0 0 0 0
0 0 0 0 0
0 0 1 0 0
0 0 1 0 0
1 1 2 1 1
1 1 2 1 1
2 2 1 2 2
2 2 1 2 2
1 1 0 1 1
1 1 0 0 0
0 0 0 0 0
0 0 0 0 0

'Remove the cell with zero

Private Sub delzero()
Dim i, j As Integer
For j = 1 To 5
For i = 1 To 1000
    If Cells(2, j) = 0 Then
     Cells(2, j).delete Shift:=xlUp
    Else
    End If
Next i
Next j
End Sub