Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look-up?? and delete (2003)

    Greetings,

    Here is what I am thinking about doing. Background: I have a spreadsheet that I have configured to load a text file. I generally do this once a week. There are approx. 700 lines of data. this will only grow in size. This is a file based upon "miminal" filtering of the data. What I would like to do is run a macro to read a list. If the items on the list are in the file I have imported, then delete the entire row.

    The matching column ' I ' beginning in Row 6. The list will be dynamic (generally growing, not reducing).

    Any thoughts? Ideas?


    Thanks,
    Brad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Look-up?? and delete (2003)

    Could you elaborate?

    I think it also would be good to attach some files for us to "play with"

    a sample text file
    A sample workbook containing:
    A worksheet with the existing data
    A worksheet with what you want at the end

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look-up?? and delete (2003)

    Could you post a sample workbook?

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look-up?? and delete (2003)

    Will post by tomorrow.

    Thanks
    Brad

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look-up?? and delete (2003)

    hey guys,

    here is the file as requested and promised.


    Brad
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Look-up?? and delete (2003)

    You don't need a macro, a simple way without code would be to:
    In a blank column in row 6 add the formula:
    =ISNUMBER(MATCH(I6,'Data List'!$A$1:$A$12,0))

    Copy/autofill it down the column

    Autofilter on this column for TRUE, and then delete these rows. Then you can remove the autofilter and delete the column and you will be left with those not in the list

    Steve

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look-up?? and delete (2003)

    Steve,
    I understand your option, but I have several macros that are running upon import of the data. Based upon that, I really need to add to the autorun method and 'macro-ize' the thought. In the macros, I have one that is not automatically ran, that perhaps could be copied and modifed to do what I need, then run with the rest. I am looking a miminal intervention of the data.

    Sub DeleteRowsIfColumnFUIsnotEmpty()
    Dim r As Long
    Dim n As Long
    n = Range("Z65536").End(xlUp).Row
    For r = n To 6 Step -1
    If Trim(Range("FU" & r)) <> "" Then
    Range("FU" & r).EntireRow.Delete
    End If
    Next r
    End Sub

    Any ideas?

    Brad

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look-up?? and delete (2003)

    Try this <pre>Public Sub DeleteRowOnMatch()
    Dim myRow As Long
    Dim oSht As Worksheet
    Dim mr As Long
    Dim myRange As Range

    Set myRange = Sheets("Data List").Range("A:A")
    Set oSht = ThisWorkbook.Sheets("Resultant Data After function")
    With oSht
    myRow = .Cells(ActiveSheet.Rows.Count, "I").End(xlUp).Row
    For myRow = myRow To 6 Step -1
    On Error Resume Next
    mr = Application.WorksheetFunction.Match(.Range("i" & myRow), myRange, 0)
    If Err Then
    On Error GoTo 0
    Else
    .Range("A" & myRow).EntireRow.Delete
    End If
    Next myRow
    End With
    Set oSht = Nothing
    Set myRange = Nothing
    End Sub
    </pre>

    Regards
    Don

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look-up?? and delete (2003)

    Don,

    That works great!!!!

    I continue to learn,

    Many thanks,

    Brad

Posting Permissions

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