Page 1 of 2 12 LastLast
Results 1 to 10 of 13

search and delete row with matching data

This is a discussion on search and delete row with matching data within the Excel Questions forums, part of the Question Forums category; I'm not sure how to phrase my question so I'll do the best I can. I have an inventory file ...

  1. #1
    New Member
    Join Date
    Apr 2011
    Posts
    9

    Question search and delete row with matching data

    I'm not sure how to phrase my question so I'll do the best I can. I have an inventory file in Excel that has four columns, the last column is called "SKU". I have a report I download from a site that has a list of SKUs and quantities that were sold, and I need to remove one of each row with the corresponding SKU from my inventory (Excel) file.

    So as an example say the report I download has the following data:

    SKU: Qty:
    A123 1
    A456 3
    A789 1

    I would want to be able to delete one row in my inventory Excel file that has "A123" in the SKU column, delete 3 rows in my inventory file that has "A456" in the SKU column, and finally delete 1 row in my inventory file that has "A789" in the SKU column. Five rows total should be removed. Anyone know how to do this?? Many thanks in advance!!
    Last edited by breakbadnm; Apr 3rd, 2011 at 01:20 AM.

  2. #2
    New Member
    Join Date
    Apr 2011
    Posts
    9

    Default Re: search and delete row with matching data

    bump

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,006

    Default Re: search and delete row with matching data

    I think I understand what you want, but what type of file is the downloaded report? Is it a text file, an Excel file, or something else (what is its file extension)? The type of file will determine how Excel VBA can read it, or not.

    Also, how many rows are in the Excel inventory file and the report file?

  4. #4
    New Member
    Join Date
    Apr 2011
    Posts
    9

    Default Re: search and delete row with matching data

    Quote Originally Posted by John_w View Post
    I think I understand what you want, but what type of file is the downloaded report? Is it a text file, an Excel file, or something else (what is its file extension)? The type of file will determine how Excel VBA can read it, or not.

    Also, how many rows are in the Excel inventory file and the report file?
    Thanks John_w for your response!

    The downloaded report is a tab-deliminated text file. It's easy enough to import it into Excel and save as a regular Excel file.

    The Excel inventory file is about 6500 rows. The report file varies, I think usually around 300-400 rows.

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,006

    Default Re: search and delete row with matching data

    Try this on a copy of your Excel inventory workbook. Put the code in a standard module in that workbook. VBA can read text files directly, so there's no need to import the report file.

    You will need to change the report folder path and file name as indicated in the code. Also, you said that the SKU column is the last of 4 columns, so I've assumed this is column D (on Sheet1 of the inventory workbook) - again modify the code where indicated.
    Code:
    Public Sub Update_Inventory()
    
        Dim reportFileName As String
        Dim fileNum As Integer
        Dim reportLine As String
        Dim parts As Variant
                   
        reportFileName = "C:\Temp\Excel\tab-delimited_report.txt"    'CHANGE AS REQUIRED
        
        fileNum = FreeFile
        Open reportFileName For Input As fileNum
        While Not EOF(fileNum)
            Line Input #fileNum, reportLine
            parts = Split(reportLine, vbTab)
            Find_and_Delete_SKU CStr(parts(0)), CInt(parts(1))
        Wend
        Close #fileNum
        
    End Sub
    
    
    Private Sub Find_and_Delete_SKU(SKU As String, numRows As Integer)
    
        Dim foundRange As Range
        Dim deletedRows As Integer
    
        deletedRows = 0
        
        'Search 'numRows' times for SKU in Sheet1 column D (the 4th column)
            
        Do
            Set foundRange = Sheets("Sheet1").Columns("D").Find(What:=SKU)    'CHANGE AS REQUIRED
            
            If Not foundRange Is Nothing Then
            
                'Delete row
                
                foundRange.EntireRow.Delete Shift:=xlUp
                deletedRows = deletedRows + 1
                
            End If
            
        Loop Until foundRange Is Nothing Or deletedRows = numRows
        
    End Sub

  6. #6
    New Member
    Join Date
    Apr 2011
    Posts
    9

    Default Re: search and delete row with matching data

    thank you so much john! will give it a try tomorrow and report back here

  7. #7
    New Member
    Join Date
    Apr 2011
    Posts
    9

    Default Re: search and delete row with matching data

    Quote Originally Posted by John_w View Post
    You will need to change the report folder path and file name as indicated in the code. Also, you said that the SKU column is the last of 4 columns, so I've assumed this is column D (on Sheet1 of the inventory workbook) - again modify the code where indicated.
    Tried it and got a run-time error '13': type mismatch. I did modify the report folder path and file name as needed, so that shouldn't be the issue. You are correct that column D in my inventory file is the SKU column.

    I think the problem may lie in the txt file report. This report, if you open it in Excel, has dozens of columns. It's not until column N that you get to the sku column (labeled 'sku' in the report), and the quantity-shipped column is 2 columns over in column P (labeled 'quantity-shipped' in the report). I imagine that will change the code you posted above? I wasn't sure how to modify it...

  8. #8
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,006

    Default Re: search and delete row with matching data

    One assumption too many... the parts(0) and parts(1) refer to the 1st and 2nd columns in the tab-delimited file, so that bit needs changing to use columns N and P. I've also told it to ignore the first record (row), as this contains column headings. Here's the amended code in full:
    Code:
    Public Sub Update_Inventory()
    
        Dim reportFileName As String
        Dim fileNum As Integer
        Dim reportLine As String, i As Long
        Dim parts As Variant
            
        reportFileName = "C:\Temp\Excel\tab-delimited_report.txt"     'CHANGE AS REQUIRED
        
        i = 0
        fileNum = FreeFile
        Open reportFileName For Input As fileNum
        
        While Not EOF(fileNum)
            Line Input #fileNum, reportLine
            i = i + 1
            If i > 1 Then
                parts = Split(reportLine, vbTab)
                Find_and_Delete_SKU CStr(parts(Asc("N") - 65)), CInt(parts(Asc("P") - 65))
            End If
        Wend
        Close #fileNum
        
    End Sub
    
    Private Sub Find_and_Delete_SKU(SKU As String, numRows As Integer)
    
        Dim foundRange As Range
        Dim deletedRows As Integer
    
        deletedRows = 0
        
        'Search 'numRows' times for SKU in Sheet1 column D (the 4th column)
            
        Do
            Set foundRange = Sheets("Sheet1").Columns("D").Find(What:=SKU)
            
            If Not foundRange Is Nothing Then
            
                'Delete row
                
                foundRange.EntireRow.Delete Shift:=xlUp
                deletedRows = deletedRows + 1
                
            End If
            
        Loop Until foundRange Is Nothing Or deletedRows = numRows
        
    End Sub

  9. #9
    New Member
    Join Date
    Apr 2011
    Posts
    9

    Default Re: search and delete row with matching data

    Appears to be working now!!

    One thing though is I keep getting this error from time to time (not sure why):



    If I remove the module, save the file, then restart Excel that error goes away but curious why it keeps popping up. Any idea?

    Regardless, thank you sooo much for your help john_w.

  10. #10
    Board Regular Alphacsulb's Avatar
    Join Date
    Mar 2008
    Posts
    356

    Default Re: search and delete row with matching data

    This might help you out in the future:

    Sub Test()
    With Intersect(Range("A:A"), ActiveSheet.UsedRange)
    .AutoFilter Field:=1, Criteria1:="a123"
    .Offset(1).EntireRow.Delete
    .AutoFilter
    End With
    End Sub

    Using column A it finds "a123" and deletes that row.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com