1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Maro between sheets

Discussion in 'Business Applications' started by adavidson, Oct 26, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. adavidson

    adavidson Thread Starter

    Joined:
    Oct 26, 2008
    Messages:
    3
    I can't write macros so your help would be appreciated.

    I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.

    I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet the entire row will be deleted from the Outstanding sheet.

    I've tried copying some macros but I keep getting them wrong.

    Please help!
    Cheers,
    Ang
     
  2. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ang, welcome to the Forum, unfortunately we need a bit more information to be able to help you. Preferably an example of your Workbook with the Column Headings shown.
    But at a bare minimum we need to know which Columns the File Number is in on both Sheets.
     
  3. adavidson

    adavidson Thread Starter

    Joined:
    Oct 26, 2008
    Messages:
    3
    The file number is unique, it is in Column H in both the Paid Sheet and the Outstanding Sheet, there is a heading row for the File No and it is row 10. Does that help?
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Perhaps something like this ....
    Code:
    Option Explicit
    
    Sub DeleteMatching()
        Dim wsOS As Worksheet, wsPD As Worksheet, rFind As Range
        Dim i As Long, iLastRow As Long, iStartRow As Long
        Set wsOS = ThisWorkbook.Sheets("Outstanding")
        Set wsPD = ThisWorkbook.Sheets("Paid")
        iLastRow = wsOS.Cells(wsOS.Rows.Count, "H").End(xlUp).Row
        'This is the row your data starts on
        iStartRow = 11
        If iLastRow < iStartRow Then Exit Sub
        Call ToggleEvents(False)
        For i = iLastRow To iStartRow Step -1
            Set rFind = Nothing
            With wsPD.Range("H:H")
                Set rFind = .Find(what:=wsOS.Cells(i, "H").Value, after:=.Cells(iStartRow, 1))
                If Not rFind Is Nothing Then
                    wsOS.Rows(i).Delete Shift:=xlUp
                End If
            End With
        Next i
        Call ToggleEvents(True)
    End Sub
    
    Public Sub ToggleEvents(blnState As Boolean)
    'Originally written by firefytr
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
    Edit: And please remember to SAVE before running this - just in case it doesn't do what you are asking.. but from what I gathered it is..

    HTH
     
  5. adavidson

    adavidson Thread Starter

    Joined:
    Oct 26, 2008
    Messages:
    3
    Thanks for your time and your help.
    However, I managed to do it using the following:
    Sub Ang()
    Dim PaidFileNums
    Dim r As Long, lr As Long

    Application.ScreenUpdating = False
    With Sheets("Paid")
    PaidFileNums = .Range("H11", .Range("H" & .Rows.Count).End(xlUp)).Value
    End With
    With Sheets("Outstanding")
    lr = .Range("H" & .Rows.Count).End(xlUp).Row
    For r = lr To 11 Step -1
    If IsNumeric(Application.Match(.Cells(r, "H").Value, PaidFileNums, 0)) Then
    .Rows(r).Delete
    End If
    Next r
    End With
    Application.ScreenUpdating = True
    End Sub
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    .. which is almost like what I posted, but not as robust. I'd still recommend looking at the code I posted.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/763143

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice