delete the data if condition met

[c@1234]

my file name is sample.xlsm
vba will be in vba.xlsm file
if column D, column E and column F of sample.xlsm file are different (not same) then keep that row and if not then delete that complete row

[HansV]

Welcome to Eileen's Lounge (or are you roger@/umpsbug/leonardo1234 ?)

See delete the rows based on conditions

[c@1234]

I am getting an error sir the problem is near to same but not same i tried but something went wrong

[c@1234]

or are you roger@/umpsbug/leonardo1234 ?
No sir

[HansV]

Please post the code you're using, and indicate which line causes the problem.

[c@1234]

i simply changed the column E,F,G to D,E,F but the code was not working

[HansV]

Please post a sample workbook (without sensitive information)

[c@1234]

plz see

[HansV]

There is no macro in the workbook...

[c@1234]

yes bcoz it was not working i simply changed that column in the code but it was not working

[HansV]

Please post a workbook including the modified macro.

[c@1234]

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

instead of E,F,G
I putted D,E, F

[c@1234]

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("D:F").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("D" & r).Value = wsh.Range("E" & r).Value And _
                wsh.Range("D" & r).Value = wsh.Range("F" & 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

[HansV]

You have to change the name of the workbook in the code from Students.xlsx to Sample.xlsm

[c@1234]

ya i done that also but this code is not working

[HansV]

What exactly is the problem? Just saying "this code is not working" doesn't tell me much.

[c@1234]

my file is not opened
as per the code file should be opened then only code will work

[HansV]

Change the line

Code: Select all

    Set wsh = Workbooks("Sample.xlsm").Worksheets(1)

to

Code: Select all

    Set wsh = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm").Worksheets(1)

[c@1234]

Sir after changing the same, the code is not working plz recheck

[HansV]

What is the problem this time? Just mentioning "the code is not working" is not specific enough.