OxDEAD Unicornz

Have you ever seen so many?

Remove Rows With 0 in the Particular Cell of Excel Document

This weekend my father complained he’s unable to write macros needed for faster Excel documents processing. He was eager to ask system administrator at work for help, but I don’t think it was a good idea. So I’ve spent several hours poking Visual Basic and Excel and made needed macros.

While other tasks were pretty easy, I’ve stuck trying to remove rows with “0” in the cells of the partucular column. Here is how I made it, maybe not the best solution, but it works:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub row_removal()

  Dim del_cell As Range, cell As Range
        
  ActiveCell.Range("AY1:AY29").Select  # Here is range selecred, relative to active cell
                                       # If cell in AY column has value "0", we'll remove row

  For Each cell In Selection
    If cell.Value = 0 Then

      If del_cell Is Nothing Then
        Set del_cell = cell
      Else
        Set del_cell = Union(del_cell, cell)
      End If

    End If
  Next cell
   
  If Not del_cell Is Nothing Then del_cell.EntireRow.Delete

End Sub