Macro to Delete From Rows from Multiple Sheets Based on cell criteria

[matnelso]

I have 3 sheets of data in a spreadsheet with the first sheet containing criteria(sheet name = Customer), the second sheet containing rolled up data (sheet name = Roll Up), and the third sheet containing PO Line Item Data (sheet name = PO Data). 'Roll Up' and 'PO Data' contain a full list of customers starting in cell B8 on both (header is row 7) with the possibility of multiple lines per customer e.g. customer A has 5 lines, customer B has 3 lines and so forth. The 'Customer' tab has a unique name in cell B3, what is a macro I can use that will delete all rows of data from 'Roll Up' and 'PO Data' that DON'T match the customer name listed on 'Customer' in cell B3.

I'm trying to take baby steps with this , because I would like to get to the point where I could have a master file and then have the macro go through and break out each unique customer and save it in a individual file, but that will come later. Please let me know if you need any additional info to help you and I appreciate your help in advance.

Thanks.

 

[portews]

Try this.

Function LastRow(sht As String) As Long
'function to determine the last row in column B
LastRow = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row
End Function


Sub DelAllBut()
'http://www.mrexcel.com/forum/excel-questions/896013-macro-delete-rows-multiple-sheets-based-cell-criteria.html
Dim sht As String
Dim Length As String
sht = "Roll Up"
For j = 1 To 2                                      'for each of the two sheets
    For i = LastRow(sht) To 8 Step -1     'delete from the bottom up to row 8
        If Not Sheets(sht).Cells(i, 2) = Sheets("Customer").Range("B3") Then  'if it doesn't match B3 then
            Sheets(sht).Cells(i, 2).EntireRow.Delete                                         'delete the entire row
        End If
    Next i
sht = "PO Data"                                   'set the name of the second sheet
Next j
End Sub

 

[matnelso]

Try this.

Function LastRow(sht As String) As Long
'function to determine the last row in column B
LastRow = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row
End Function


Sub DelAllBut()
'http://www.mrexcel.com/forum/excel-questions/896013-macro-delete-rows-multiple-sheets-based-cell-criteria.html
Dim sht As String
Dim Length As String
sht = "Roll Up"
For j = 1 To 2                                      'for each of the two sheets
    For i = LastRow(sht) To 8 Step -1     'delete from the bottom up to row 8
        If Not Sheets(sht).Cells(i, 2) = Sheets("Customer").Range("B3") Then  'if it doesn't match B3 then
            Sheets(sht).Cells(i, 2).EntireRow.Delete                                         'delete the entire row
        End If
    Next i
sht = "PO Data"                                   'set the name of the second sheet
Next j
End Sub

That code does work, however there will be anywhere from 30k to 200k lines per file so by going 1 line at a time it takes forever to complete. Is there something different that can be done knowing that the list of customers is sorted i.e. goes from line 200k up to 33k, then has 11k of correct customer, and then deletes the rest after that?

 

[matnelso]

I am assuming that using the autofilter function would probably be much faster, but I must admit I'm no expert on this function of excel and being able to incorporate it into vba code