excel macro to delete 0's based on columns

Hi,

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



Thanks

Comments

  • try this and see download file-

    [code]
    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
    Next

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

    End If
    Next

    End Sub
    [/code]
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!

Categories

In this Discussion