Solved

Delete criteria change to code

Posted on 2013-05-29
5
177 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
  • 3
  • 2
5 Comments
 
LVL 39

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 39

Accepted Solution

by:
als315 earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now