Code to delete rows under my scenario

[Liosis]

Hello all,

I've been trying to write a macro for my purposes, but have been having no luck. Please help if you can...here is my exact situation.

I need to be able to delete all rows where the following conditions are FALSE. (D10:D65536 is equal to the value of cell $D$7) and (AA10:AA65536 is equal to 0 or 1). Column AA will have empty cells that I do not want to be mistaken as 0. There are no breaks in the data in column D, and as such, it may be a good deal quicker to use a selected range of D10 to the last non-empty cell in the column.

I'll be elated if I could get some help...it's been killing me!

 

[Joe4]

Welcome to the Board!

I am not sure if I completely clear on your conditions (do both conditions need to be FALSE to delete the row, or just one?).

If both, then I think this should work:

Sub MySpecialDelete()
 
    Dim myLastRow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column D
    myLastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through rows backwards, deleting rows
    For i = myLastRow To 10 Step -1
        If (Cells(i, "D") <> Range("D7")) And _
                (Cells(i, "AA") <> 0) And _
                (Cells(i, "AA") <> 1) And _
                (Len(Cells(i, "AA")) > 0) Then
            Rows(i).EntireRow.Delete
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

 

[Rick Rothstein]

Like Joe, I wasn't completely clear on your conditions either. Would it be correct to say you want to delete all rows where Column D's value is not equal to D7's value AND Column AA's value is not equal to either 0 or 1. IF that is true, then I think this macro should do what you want...

Sub DeleteRows()
  Dim UnusedColumn As Long
  ' Find an empty column
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(10, UnusedColumn).Resize(Rows.Count - 9)
    ' Fill that empty column with a formula that puts an X in its cell for those rows meeting the stated condition
    .FormulaR1C1 = "=IF(AND(RC4<>R7C4,RC27<>{0,1}),""X"","""")"
    ' Convert the formulas in the unused column to their displayed value
    .Value = .Value
    ' Protect against the next line not finding anything
    On Error Resume Next
    ' Locate all cells in the unused column with an X in it and delete the entire row
    .SpecialCells(xlConstants).EntireRow.Select '.Delete
  End With
End Sub

 

[Liosis]

Sorry about the confusion...my wording wasn't as clear as it could have been...in fact, it was wrong.

Let me explain the manual process so it's more clear. Right now I sort the data by Column D and delete all rows not equal to $D$7. Next, I sort the data by column AA from smallest to largest and delete all rows that do not have a 0 or 1 in column AA. The end result is that I'm left with rows that have both the $D$7 value in column D cells and a (0 or 1) in column AA cells.

Therefore, I think it's safe to say that I want to delete rows where (cells in Column D <> $D$7) or (cells in column AA <> (0 or 1)). If either one of these conditions is true then delete row.

Thanks for your patience, I'm new to the macro side of excel, and am realizing just how powerful excel can be! Hopefully this explanation helps.

 

[Rick Rothstein]

No time now to set anything up for testing, but does this do what you want?

Sub DeleteRows()
  Dim UnusedColumn As Long
  ' Find an empty column
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(10, UnusedColumn).Resize(Rows.Count - 9)
    ' Fill that empty column with a formula that puts an X in its cell for those rows meeting the stated condition
    .FormulaR1C1 = "=IF(OR(RC4<>R7C4,RC27<>{0,1}),""X"","""")"
    ' Convert the formulas in the unused column to their displayed value
    .Value = .Value
    ' Protect against the next line not finding anything
    On Error Resume Next
    ' Locate all cells in the unused column with an X in it and delete the entire row
    .SpecialCells(xlConstants).EntireRow.Select '.Delete
  End With
End Sub

 

[Joe4]

Therefore, I think it's safe to say that I want to delete rows where (cells in Column D <> $D$7) or (cells in column AA <> (0 or 1)). If either one of these conditions is true then delete row.

A minor modification to my code should do that too:

Sub MySpecialDelete()
 
    Dim myLastRow As Long
    Dim i As Long
 
    Application.ScreenUpdating = False
 
'   Find last row in column D
    myLastRow = Cells(Rows.Count, "D").End(xlUp).Row
 
'   Loop through rows backwards, deleting rows
    For i = myLastRow To 10 Step -1
        If (Cells(i, "D") <> Range("D7")) Or _
                ((Cells(i, "AA") <> 0) And _
                (Cells(i, "AA") <> 1) And _
                (Len(Cells(i, "AA")) > 0)) Then
            Rows(i).EntireRow.Delete
        End If
    Next i
 
    Application.ScreenUpdating = True
 
End Sub

 

[Liosis]

So I finally found some down time to try these. I had to make a few adjustments, but this works like a charm. Thank you so much for your help...I really appreciate it!!

Sub DeleteRows()
 Dim UnusedColumn As Long
  ' Find an empty column
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(10, UnusedColumn).Resize(Rows.Count - 9)
    ' Fill that empty column with a formula that puts an X in its cell for those rows meeting the stated condition
    .FormulaR1C1 = "=IF(SUM((RC[-25]=R7C4)+(RC[-2]=0)+(RC[-2]=1)+ISNUMBER(RC[-2]))=3,"""",""X"")"
    ' Convert the formulas in the unused column to their displayed value
    .Value = .Value
    ' Protect against the next line not finding anything
    On Error Resume Next
    ' Locate all cells in the unused column with an X in it and delete the entire row
    .SpecialCells(xlConstants).EntireRow.Select
    Selection.Delete
    Range("A10").Select
  End With
End Sub

 

[Rick Rothstein]

I had to make a few adjustments, but this works like a charm.

    .SpecialCells(xlConstants).EntireRow.Select
    Selection.Delete

I apologize for having left the Select method in my originally posted code instead of the Delete method you wanted (I used Select so I didn't lose any data while testing the code). The above two quoted lines of code can be replaced with this single line of code...

    .SpecialCells(xlConstants).EntireRow.Delete

 

[Liosis]

No apologies needed...your code was a lot of help to me. Thanks again!