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