?
Solved

Delete Rows based on 2 columns

Posted on 2011-09-12
8
Medium Priority
?
235 Views
Last Modified: 2012-05-12
I found this code on this site (I've modified),

If Column A is duplicated, delete only the rows with the word "No Constraint" in Column 13.

See attached file, the rows highlighted are the ones that should be deleted.
Sub DeleteDupRows()

Dim i
Dim J
Dim RowsA
Dim Val1
Dim Val2


    Application.ScreenUpdating = False
RowsA = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To RowsA
    Val1 = Cells(i, 2)

    For J = i + 1 To RowsA
        Val2 = Cells(J, 2)
        If Val1 = Val2 And Cells(i, 13) = "No Constraints" Then
            Rows(J).EntireRow.Delete
        End If
    Next J
Next i
    Application.ScreenUpdating = True
End Sub

Open in new window

TEST-1.xls
0
Comment
Question by:mato01
[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
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 36525432
Try this code in place of yours...

Sub HideDupRows()

Dim i
Dim J
Dim RowsA
Dim Val1
Dim Val2


    Application.ScreenUpdating = False
RowsA = Cells(Rows.Count, 2).End(xlUp).Row
For i = RowsA To 1 Step -1
    Val1 = Cells(i, 2)

    For J = RowsA To 1 Step -1
        Val2 = Cells(J, 2)
        If Val1 = Val2 And Cells(J, 10) = "No Constraints" Then
            Rows(J).EntireRow.Hidden = True
        End If
    Next J
Next i
    Application.ScreenUpdating = True
End Sub
0
 
LVL 14

Expert Comment

by:JP
ID: 36525510
See attached code
Sub RemoveDupRows()

Dim i
Dim lastrow
 Application.ScreenUpdating = False
lastrow = [b65536].End(xlUp).Row
For i = lastrow To 2 Step -1
    If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
        If Cells(i, "J").Value = "No Constraints" Then
            Rows(i).Delete
        ElseIf Cells(i - 1, "J").Value = "No Constraints" Then
            Rows(i - 1).Delete
        End If
    End If
Next i
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:mato01
ID: 36527015
This works, except that if A is not a duplicate it deletes the row with "No Constraints" anyway.
0
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!

 
LVL 14

Expert Comment

by:JP
ID: 36527669
When I test it does work as it should when "A" is duplicated. When I tested with your file I go from 15 rows to 13 with only the two highlighted rows being removed. But if I put "No Constraints" in cell "J4" then row 4 will be removed as well. This is as you have asked for because "A4" & "A5" are the same even though "B4 & B5" are different groups. If we need to look at column B as well just let us know.
0
 

Author Comment

by:mato01
ID: 36527898
I need both conditions to be true for the row to delete.

1. Cell A needs to be a duplicate entry

2. Cell J needs to havew the word "No Constraint".  

What the code is doing in both sets of script is deleteing/hiding every row with the word "No Constraints", regardless of Cell A is a duplicate. See attached.  What's in yellow should delete or hide, and rows shaded in green should not delete or hide.  If you run the script you will see that it deletes all those rows.

TEST-1-2-.xls
0
 
LVL 14

Accepted Solution

by:
JP earned 1000 total points
ID: 36528018
If you look at cell A7 & A8 they are the same so since J7 contains "No Constraints" it is deleted, same with A14 & A15. Will B always match for the records you want to remove? In your previous example the values in B were sorted or appeared to be and were not duplicated. Is it possible to sort them before checking the values of J or not?
0
 

Author Comment

by:mato01
ID: 36530657
Yes.  How can I add to below toI sort columns A thru M prior to running the remove dup.  If not, I can just run another macro prior to running Sub RemoveDupRows




Sub RemoveDupRows()

Dim i
Dim lastrow
 Application.ScreenUpdating = False
lastrow = [b65536].End(xlUp).Row
For i = lastrow To 2 Step -1
    If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
        If Cells(i, "J").Value = "No Constraints" Then
            Rows(i).Delete
        ElseIf Cells(i - 1, "J").Value = "No Constraints" Then
            Rows(i - 1).Delete
        End If
    End If
Next i
    Application.ScreenUpdating = True
End Sub
0
 
LVL 14

Expert Comment

by:JP
ID: 36530843
You can add this code to the code above before the For i statement. I did not have time to test this code it was modified from another macro I have. If your sheet is not named Sheet1 then replace the name throughout this code. You can add or remove the number of sort keys easily.

    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear 'Change Sheet1 to Sheet Name
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B" & lastrow), _ 'Change Sheet1 to Sheet Name
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & lastrow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:M" & lastrow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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