excel macro to delete 0's based on columns


I have an excel file that has 15 columns , the first 3 will contain data but the next 12 are the periods of the month period 1 to 12 and I want a macro to see if period 01 to period 12 are all 0's to delete that entire row. Can you please let me know what the vba code would be, i dont want it sorted I need it to be in the exam same order but if P01 to p02 are all 0's then to delete that whole row including the first 3 columns that contain data



  • try this and see download file-

    Private Sub CommandButton1_Click()
    Dim myCol As Integer
    Dim myRow As Integer
    Dim ColCount As Byte
    Dim myRange As String

    'work from bottom to top, since rows are being deleted
    For myRow = 10 To 4 Step -1
    'only test row if first column contains a value
    If Not IsNull(Application.Cells(1, myRow)) Then

    'init count
    ColCount = 0

    'traverse cols 4-15 [months 1-12]
    For myCol = 4 To 15
    'cell contains zero? count that cell
    If Val(Application.Cells(myRow, myCol)) = 0 Then _
    ColCount = ColCount + 1

    '12 zeros counted?
    If ColCount = 12 Then
    'select and delete a range of cells w/ 12 zeros
    myRange = "A" & myRow & ":" & "O" & myRow
    Selection.Delete Shift:=xlUp
    End If

    End If

    End Sub
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion