Macro Loop HELP

[moonlight22]

Hi,

I have the following macro which I'm using to find a value that is in my ControlSheet in cell B11, in column M of the Datasheet, and then it deletes the row. It also copies the row to the RecordsRemoved tab before completing the macro.

My requirements have changed and now i need this macro to find 4 values, not just B11. The following chart summarizes where the value is in the ControlSheet and which column in the Datasheet it needs to search. I need the same action to occur that is happening today, where any row which has the 4 criteria is deleted from the Datasheet, and the row is copied to the RecordsRemoved.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ControlSheet
[/TD]
[TD]DataSheet[/TD]
[/TR]
[TR]
[TD]I12[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]I13[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]I14[/TD]
[TD]Column I[/TD]
[/TR]
[TR]
[TD]I15[/TD]
[TD]Column J[/TD]
[/TR]
</tbody>[/TABLE]

I could not figure out the loop...
any help will be greatly appreciated!

Sub DeletingRows()

Dim Rng As Variant, CriteriaValue As String


    CriteriaValue = Sheets("ControlSheet").Range("B11").Value
    If CriteriaValue = "" Then
        MsgBox "No Criteria Entered", vbOKOnly
        Exit Sub
    End If
    Err.Clear
    On Error Resume Next
    Rng = Sheets("DataSheet").Range("M:M").Find(CriteriaValue, Sheets("DataSheet").Range("M1"), xlValues, xlWhole)
    If Err.Number > 0 Then
        MsgBox "No instances were found.", vbOKOnly, "Nothing Found"
        Exit Sub
    End If
    With Sheets("DataSheet")
        Do Until Rng Is Nothing
            Set Rng = Nothing
            Set Rng = .Range("M:M").Find(CriteriaValue, .Range("M1"), xlValues, xlWhole)
            If Not Rng Is Nothing Then
                Rng.EntireRow.Copy
                Sheets("Records Removed").Range("A" & Sheets("Records Removed").Range("M" & Rows.Count).End(xlUp).Row + 1).PasteSpecial
                Rng.EntireRow.Delete
            End If
        Loop
    End With


End Sub

 

[yky]

Is this an AND condition or OR condition? I mean is it like "if (found I12 in Column B) And (found I13 in Column F) ...." then delete or "if (found I12 in Column B) Or (found I13 in Column F)..." then delete? If it is OR condition, just run the loop four times, each time with a different condition.

 

[moonlight22]

Is this an AND condition or OR condition? I mean is it like "if (found I12 in Column B) And (found I13 in Column F) ...." then delete or "if (found I12 in Column B) Or (found I13 in Column F)..." then delete? If it is OR condition, just run the loop four times, each time with a different condition.

Hi there, it is an "AND" condition. All the conditions must be met in order for the row to be deleted.

Many thanks!!

 

[ExcelGuyDude]

Would something like this work?

Sub DeletingRows()

    Dim LastRow As Long, FirstRow As Long, CtrlSht As Worksheet, DataSht As Worksheet
    Dim Crit1 As String, Crit2 As String, Crit3 As String, Crit4 As String, DeleteCount As Long
    
    DeleteCount = 0
    
    Set DataSht = Sheets("DataSheet")
    Set CtrlSht = Sheets("ControlSheet")
    Crit1 = CtrlSht.Range("I12").Value
    Crit2 = CtrlSht.Range("I13").Value
    Crit3 = CtrlSht.Range("I14").Value
    Crit4 = CtrlSht.Range("I15").Value
    
    FirstRow = 1 'Enter your starting row for the loop to occur on
    LastRow = DataSht.Range("B" & startrow).SpecialCells(xlCellTypeLastCell).Row
    
    For i = LastRow To FirstRow Step -1
        With DataSht
            If .Range("B" & i).Value = Crit1 And .Range("F" & i).Value = Crit2 And .Range("I" & i).Value = Crit3 And .Range("J" & i).Value = Crit4 Then
                DeleteCount = DeleteCount + 1
                .Rows(i).EntireRow.Copy
                Sheets("Records Removed").Range("A" & Sheets("Records Removed").Range("M" & Rows.Count).End(xlUp).Row + 1).PasteSpecial
                .Rows(i).EntireRow.Delete
        End With
    Next i   
 
    If DeleteCount = 0 Then MsgBox "No instances were found.", vbInformation + vbOKOnly, "Nothing Deleted"

End Sub