?
Solved

Delete criteria change to code

Posted on 2013-05-29
5
Medium Priority
?
221 Views
Last Modified: 2013-05-29
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
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39205447
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
 

Author Comment

by:leezac
ID: 39205516
Thanks,  I just thought - can I put <> "Manual" or "Duplicate")
in this line?

If (Sheets("Compare").Cells(objCell.Row, "B") <> "Manual")
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 39205528
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
 

Author Comment

by:leezac
ID: 39205532
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
 

Author Closing Comment

by:leezac
ID: 39205884
These were answers that would work, but I need to repost the question.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question