PDA

View Full Version : Macro to delete rows if criteria is referenced in other sheets?



jsabo
03-20-2013, 11:09 AM
Hello,
I have code below which, as of now, correctly deletes rows based on criteria mentioned in other sheets in the same workbook.
I.e, the code looks at column B for a document number. If that document number is contained anywhere in the previous sheets, the entire row containing that document number is deleted.
The problem I am having is that if all the criteria is mentioned previously in other sheets, no rows are deleted. Please see the code below and suggest how I can fix it. I want the code to basically leave a blank sheet instead of not deleting any rows.
Please let me know if I can clarify:


Sheets("Misc").Select
k = 2
Do While k <= Sheets("Upcoming").Range("B65536").End(xlUp).Row
cnt = 0
For Each ws In ActiveSheet.Parent.Worksheets
If ws.Name = ActiveSheet.Name Then Exit For
cnt = cnt + Application.WorksheetFunction.CountIf(ws.Range("B:B"), ActiveSheet.Range("B" & k).Value)
If cnt > 0 Then Exit For
Next ws
If cnt > 0 Then
ActiveSheet.Rows(k).Delete
Else
k = k + 1
End If
Loop
Range("A1").Select

SamT
03-20-2013, 02:21 PM
Sub jsabo1()

'Don't know why you are selecting "Misc." It's not referenced in this segment.
Sheets("Misc").Select

Dim ActWkBk As Workbook
Set ActWkBk = ActiveSheet.Parent 'Means this code is not in the active Workbook

Dim ws As Worksheet
Dim ThisSht As Worksheet 'The one we're deleting rows from
Set ThisSht = ActWkBk.ActiveSheet
Dim Found As Range

Dim k As Long
k = 2
Dim LastK As Long
LastK = ThisSht.Range("B65536").End(xlUp).Row

Do While LastK => k 'Count backwards so you don't skip any rows
'If you delete while counting forwards, you skip the
'Row that moved into Row(k) position and your
'count gets messed up. ie. LastK is bigger than
'Rows.Count(After deletions)

For Each ws In ActWkBk.Worksheets
If ws = ThisSht Then GoTo SkipThisWkSht
'Setting After:="B2" because k = 2. YMMV
Set Found = ws.Range("B:B").Find(After:="B2", _
What:=ThisSht.Range("B" & LastK).Value, _
LookAt:=xlWhole)
If Not Found Is Nothing Then
ActiveSheet.Rows(LastK).Delete
Exit For
End If
SkipThisWkSht:
Next ws
LastK = LastK - 1
Loop
Range("A1").Select 'On which Worksheet in which workbook
End Sub