Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows in multiple worksheets | Excel Programming | |||
Inserting/deleting rows between worksheets | Excel Worksheet Functions | |||
deleting rows across worksheets? | Excel Programming | |||
Question about deleting rows from sorted linked worksheets | Excel Worksheet Functions | |||
Deleting a rows from multiple worksheets | Excel Worksheet Functions |