[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Delete criteria change to code

Is there a way to add to the delete portion of the code an exception "not to delete the row on the Accounts tab where the word "Manual" is in column B".

somehow this this row    "If Not (IsEmpty(Sheets("Compare").Cells(objCell.Row, "AP"))) Then
               Sheets("Accounts").Rows(lngRow).EntireRow.Delete

Thanks in advance.
________________________________________________________________

Sub DeleteMissingAccounts()

  Dim lngRow                                            As Long
  Dim objCell                                           As Range
  Dim objRange                                          As Range
 
  On Error Resume Next
    Application.ScreenUpdating = False
 
   Set objRange = Range(Sheets("Compare").[c3], Sheets("Compare").Cells(Sheets("Compare").Cells.Rows.Count, "C").End(xlUp))
 
 If Not (objRange Is Nothing) Then
     Sheets("Accounts").Select
 
     For lngRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row To 2& Step -1&
 
         Set objCell = Nothing
         Set objCell = objRange.Find(What:=Cells(lngRow, "A"))
     
         If Not (objCell Is Nothing) Then
            If Not (IsEmpty(Sheets("Compare").Cells(objCell.Row, "AP"))) Then
               Sheets("Accounts").Rows(lngRow).EntireRow.Delete
            End If
         End If        
     Next lngRow
     
 End If ' If Not (objRange Is Nothing) Then
0
leezac
Asked:
leezac
  • 3
  • 2
1 Solution
 
als315Commented:
Change
If Not (IsEmpty(Sheets("Compare").Cells(objCell.Row, "AP"))) Then
to:
If (Sheets("Compare").Cells(objCell.Row, "B") <> "Manual") And (Not (IsEmpty(Sheets("Compare").Cells(objCell.Row, "AP")))) Then

Open in new window

0
 
leezacAuthor Commented:
Thanks,  I just thought - can I put <> "Manual" or "Duplicate")
in this line?

If (Sheets("Compare").Cells(objCell.Row, "B") <> "Manual")
0
 
als315Commented:
You can add both criterias:
If (Sheets("Compare").Cells(objCell.Row, "B") <> "Manual") AND (Sheets("Compare").Cells(objCell.Row, "B") <> "Duplicate")

Open in new window

0
 
leezacAuthor Commented:
als315 that does not work

If "Manual" is in Column B  of the Accounts tab (has nothing to do with the Compare tab) I just want to be able to keep it and not delete it when the other rows are deleted.
0
 
leezacAuthor Commented:
These were answers that would work, but I need to repost the question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now