search and delete row with matching data

[breakbadnm]

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!!

 

[breakbadnm]

bump

 

[John_w]

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?

 

[breakbadnm]

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.

 

[John_w]

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.

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

 

[breakbadnm]

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

 

[breakbadnm]

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...

 

[John_w]

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:

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

 

[breakbadnm]

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.

 

[Alphacsulb]

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.