Consulting

Results 1 to 15 of 15

Thread: Macro to delete entire Row on Sheet 1 if values found on Sheet 2 matches

  1. #1

    Unhappy Macro to delete entire Row on Sheet 1 if values found on Sheet 2 matches

    I'm new to working with Excel Macros and I'm trying to find out how to create a macro to delete entire rows in Sheet 1, if any of the values found in ‘Sheet 2, column A’, matches any of the values found in ‘Sheet 1, column A’. I've been trying to figure this out for several hours. Can anyone help?

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [vba]
    With Worksheets("Sheet2")

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow

    On Error Resume Next
    pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"),0)
    On Error Goto 0
    If pos > 0 Then

    Worksheets("Sheet1").Rows(i).Delete
    End If
    Next i
    End With[/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thanks for replying on this. I tried it but I'm getting a syntax error on line 13. Can you tell me what I'm doing wrong? See below:

    Sub Tony_RowDel()
    With Worksheets("Sheet2")
    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow
    On Error Resume Next
    Pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"), 0)
    On Error GoTo 0
    If Pos > 0 Then
    Worksheets("Sheet1").Rows(i).Delete
    End If
    Next i
    End With
    End Sub

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    My error, should have been Pos

    [vba]

    Sub Tony_RowDel()
    With Worksheets("Sheet2")
    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
    On Error Resume Next
    Pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"), 0)
    On Error GoTo 0
    If Pos > 0 Then
    Worksheets("Sheet1").Rows(Pos).Delete
    End If
    Next i
    End With
    End Sub[/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Still getting Run-time error '13'

    Type Mismatch

    When I look up this error on the internet, I get the following possible causes:

    You need to convert the text to a numeric value before you can do anything with it.
    So,
    If cbo1.Text <= 12 Then
    would need to be something like
    If val(cbo1.Text) <= 12 Then
    And
    lbl1 = Val(cbo1.Text * 5)
    something like
    lbl1 = Val(cbo1.Text )* 5


    --------------
    Dim Val as Integer
    If IsNumber(Cbo2.Text) then
    Val = CType(Cbo2.Text, Integer)
    End If

    then use Val instead of cbo2.text


    -----------------------------------------
    Does any of this makes sense to you?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Where does text figure in your original code?

    Can you post the workbook?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    See abbreviated copy of the file attached. The original has about 25,000 rows in Sheet1 and about 1000 rows in Sheet2.

    Thank you for hanging in there with me.
    Attached Files Attached Files

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    I meant a workbook with the code, I want to see what you have done.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Sorry, hear it is again with the code. See attached.
    Attached Files Attached Files

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Try this

    [vba]

    Sub Tony_RowDel()
    Dim rng As Range
    Dim pos As Long

    With Worksheets("Sheet2")
    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
    pos = 0
    On Error Resume Next
    pos = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet1").Columns("A"), 0)
    On Error GoTo 0
    If pos > 0 Then
    If rng Is Nothing Then

    Set rng = Worksheets("Sheet1").Rows(pos)
    Else

    Set rng = Union(rng, Worksheets("Sheet1").Rows(pos))
    End If
    End If
    Next i

    If Not rng Is Nothing Then rng.delet
    End With
    End Sub[/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Wow, this worked! Thank you. The only issue I had is that since I had duplicate instances of the same value in Sheet1 Column A, I had to run the script several times until all the duplicates were removed. Nevertheless, it worked. I'll spend time figuring out exactly how you did it, so I can learn from this. Thank you again

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    If that is the case, you can invert the code, lookup from sheet1 and delete when match. That way it will only need 1 pass.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    Thanks, I'll try to figure out how to do that. Still struggling as a newbie but I'll get there.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Just remember that when deleting in a loop on Sheet1, you need to work from the bottom up, not top down as usual in a loop.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Thank you. You've been a great help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •