Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Delete row base on other sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete row base on other sheet

    Also would it be possible to clean the list in excwos before deleteing the rows in open vendor jobs?

  2. #12
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete row base on other sheet

    Actually I just found out that the parameters I was instructed to use will not work.
    Can you please trim the macro for me. I do not need to clean the EXCWOS sheet.
    So I only need the first half of the macro.
    Thanks.

  3. #13
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,299
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Delete row base on other sheet

    Code:
    Sub DeleteRows()
        Application.ScreenUpdating = False
        Dim srcWB As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, x As Long
        Set desWS = ThisWorkbook.Sheets("Open Vendor Jobs")
        LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Dim Rng As Range, RngList As Object
        Set RngList = CreateObject("Scripting.Dictionary")
        Set srcWB = Workbooks.Open("C:\Users\edward.selkov\Working\ExcludedWo's.xlsm")
        Set srcWS = Sheets("EXCWOS")
        For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
            If Not RngList.Exists(Rng.Value) Then
                RngList.Add Rng.Value, Nothing
            End If
        Next Rng
        For x = LastRow To 2 Step -1
            If RngList.Exists(desWS.Cells(x, 1).Value) Then
                desWS.Rows(x).EntireRow.Delete
            End If
        Next x
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #14
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete row base on other sheet

    Awesome. thank you.
    One last thing... How can I save and close this workbook without prompts?

  5. #15
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,299
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Delete row base on other sheet

    Add these lines of code:
    Code:
     Application.DisplayAlerts = False
        ActiveWorkbook.Save
        Application.DisplayAlerts = True
    below:
    Code:
     Next x
    Please note that this will over-write any existing copy without any warning.
    Last edited by mumps; Dec 10th, 2018 at 02:01 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #16
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete row base on other sheet

    I tried that but it did not work. the active workbook returns the personal.xls. How can I specift that workbook?

  7. #17
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,299
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Delete row base on other sheet

    Which workbook contains the macro?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #18
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete row base on other sheet

    The personal Macro Book

  9. #19
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,299
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Delete row base on other sheet

    Try using the full workbook name like this:
    Code:
    Workbooks("workbookName.xlsx").Save
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #20
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete row base on other sheet

    tks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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