Deleting Rows across Several Worksheets if Value = 0
 

Good morning

I have a workbook that is filled wih with 40+ worksheets showing Client
transactions. I am trying to find a method that will go into each of these
worksheets and delete any rows where the value in Column E is equal to zero.

The slight added difficulty is that there are 5 worksheets (named:
"MasterNonDMA%", "MasterNonDMA", "MasterDMA%", "MasterDMA", "Reciept Saxo")
that are different and therefore I would like excluded from this macro.

I really REALLY would appreciate the help.

Thank you

Regards

Deleting Rows across Several Worksheets if Value = 0
 

Make a copy (I always keep a copy before running a deletion macro...) & try
this:

Sub test()

Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim k As Integer

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "MasterNonDMA%", "MasterNonDMA", "MasterDMA%", "MasterDMA",
"Reciept Saxo"
Debug.Print "Sheet skipped"

Case Else
i = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 0 To i
If ws.Range("E1").Offset(k, 0).Value = 0 Then
ws.Range("E1").Offset(k, 0).EntireRow.Delete
Else
k = k + 1
End If
Next j

End Select

Next ws

End Sub
"PVANS" wrote:

Good morning

I have a workbook that is filled wih with 40+ worksheets showing Client
transactions. I am trying to find a method that will go into each of these
worksheets and delete any rows where the value in Column E is equal to zero.

The slight added difficulty is that there are 5 worksheets (named:
"MasterNonDMA%", "MasterNonDMA", "MasterDMA%", "MasterDMA", "Reciept Saxo")
that are different and therefore I would like excluded from this macro.

I really REALLY would appreciate the help.

Thank you

Regards

Deleting Rows across Several Worksheets if Value = 0
 

The below macro will work on the active workbook. Try and feedback
strSheets is a string variable to store the sheet names...

Sub DeleteRows()
Dim strSheets As String, lngLastRow As Long
strSheets = "MasterNonDMA%,MasterNonDMA,MasterDMA%,MasterDMA,R eciept Saxo)"

For Each ws In Worksheets
If InStr(1, "," & strSheets & ",", "," & ws.Name & ",", vbTextCompare) = 0
Then
lngLastRow = ws.Cells(Rows.Count, "E").End(xlUp).Row
For lngRow = lngLastRow To 2 Step -1
If ws.Range("E" & lngRow).Text = "0" Then ws.Rows(lngRow).Delete
Next
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

Good morning

I have a workbook that is filled wih with 40+ worksheets showing Client
transactions. I am trying to find a method that will go into each of these
worksheets and delete any rows where the value in Column E is equal to zero.

The slight added difficulty is that there are 5 worksheets (named:
"MasterNonDMA%", "MasterNonDMA", "MasterDMA%", "MasterDMA", "Reciept Saxo")
that are different and therefore I would like excluded from this macro.

I really REALLY would appreciate the help.

Thank you

Regards