delete the rows based on conditions

[roger@]

If columnE,columnF,columnG of Students.xlsx are not equal then keep that rows and delete the rest rows data
Code will be placed in different file Marks.xlsm which is opened
and targeted file is also opened and that file name is Students.xlsx

[HansV]

Here is a macro. I have added lots of comments.

Please make a backup of Students.xlsx before you test the macro.

Code: Select all

Sub DeleteRows()
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Dim rng As Range
    ' Refer to first sheet in Student.xlsx
    Set wsh = Workbooks("Students.xlsx").Worksheets(1)
    ' Find last used row in columns E to G
    m = wsh.Range("E:G").Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    ' Loop through the rows, starting in row 2
    ' If you want to start in row 1, change 2 to 1
    For r = 2 To m
        ' Check whether columns E, F and G have the same value
        If wsh.Range("E" & r).Value = wsh.Range("F" & r).Value And _
                wsh.Range("E" & r).Value = wsh.Range("G" & r).Value Then
            ' Add cell in column A to the range to be deleted
            If rng Is Nothing Then
                Set rng = wsh.Range("A" & r)
            Else
                Set rng = Union(rng, wsh.Range("A" & r))
            End If
        End If
    Next r
    ' Do we have anything to delete?
    If Not rng Is Nothing Then
        ' If so, delete the entire row(s)
        rng.EntireRow.Delete
    End If
End Sub

[BOOBALAN.V]

Dear sir,
Could you please explain this codes. I don't understand.

Code: Select all

If rng Is Nothing Then
                Set rng = wsh.Range("A" & r)
            Else
                Set rng = Union(rng, wsh.Range("A" & r))
            End If
        End If
    Next r
    ' Do we have anything to delete?
    If Not rng Is Nothing Then
        ' If so, delete the entire row(s)
        rng.EntireRow.Delete
    End If

[HansV]

If the values in columns E, F, and G are the same, we want to add a cell in the same row to the range rng. I used the cell in column A for this.

When the macro starts, the range rng is Nothing (empty). So we set rng to the cell in column A:

Set rng = wsh.Range("A" & r)

If rng has already been filled with one or more cells, we add the cell in column A to rng, by taking the union of rng and that cell:

Set rng = Union(rng, wsh.Range("A" & r))

At the end of the loop, rng might still be Nothing (empty). If so, no rows have to be deleted, so we don't need to do anything.
But if rng is not empty, it consists of all cells in column A for which the corresponding cells in columns E to G are the same.
So we delete the rows:

rng.EntireRow.Delete

[BOOBALAN.V]

Sorry to bother. Why we need to add cell in column A. Still I don't understand sir

[HansV]

It's just a way to create a collection of the rows we need to delete. We could have used any cell in those rows; I chose column A, but any column would have been OK.

It is also possible to add the entire row to the range rng, instead of just the cell in column A:

Code: Select all

Sub DeleteRows()
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Dim rng As Range
    ' Refer to first sheet in Student.xlsx
    Set wsh = Workbooks("Students.xlsx").Worksheets(1)
    ' Find last used row in columns E to G
    m = wsh.Range("E:G").Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    ' Loop through the rows, starting in row 2
    ' If you want to start in row 1, change 2 to 1
    For r = 2 To m
        ' Check whether columns E, F and G have the same value
        If wsh.Range("E" & r).Value = wsh.Range("F" & r).Value And _
                wsh.Range("E" & r).Value = wsh.Range("G" & r).Value Then
            ' Add the row to the range to be deleted
            If rng Is Nothing Then
                Set rng = wsh.Rows(r)
            Else
                Set rng = Union(rng, wsh.Rows(r))
            End If
        End If
    Next r
    ' Do we have anything to delete?
    If Not rng Is Nothing Then
        ' If so, delete the row(s)
        rng.Delete
    End If
End Sub

[BOOBALAN.V]

Okay let me explain as much as my understanding. Please correct me if I am wrong. First it will check the column E to G whether the values are same or not. If the values are same then it will set that particular Row(r) into rng. Because initially the rng will be empty. So it will execute if condition to store in rng. Again if it find another row with same value then it will go to Else if function. Likewise If it find more than one rows with same values then it will union (like holding shift key while we select which rows we want) the number rows which have the same value. Then finally it will delete all those rows at a one time. AM I right sir?

[HansV]

Yes, that is correct!

[BOOBALAN.V]

Thank you sir