Delete Rows Based on Cell Value from Another Sheet with Macro

[rangequestion]

Hi,

Hopefully this will be n easy question. In my active sheet I have an identifier in cell D5. Based on that ID I want to delete all rows in a sheet called "All Remotely Booked Txn Table" that do not have that ID in column B.

For example, if my ID is 982 I want the macro to delete all rows that do not have 982 in column B of tab called "All Remotely Booked Txn Table".

Thanks!

 

[mumps]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("All Remotely Booked Txn Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("All Remotely Booked Txn Table").Range("B1:B" & LastRow).AutoFilter Field:=1, Criteria1:="<>5"
    Sheets("All Remotely Booked Txn Table").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If Sheets("All Remotely Booked Txn Table").AutoFilterMode Then Sheets("All Remotely Booked Txn Table").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

The macro assumes you have headers in row 1 and the data starts in row 2.

 

[rangequestion]

Perfect!!! Thanks!

 

[mumps]

You are very welcome.

 

[rangequestion]

Quick follow up question here. In the event that there are matches to the criteria above can we also filter for a date range?

In the active sheet I have a start date in cell D12 and end date in D13 Id only like to keep rows where the dates in Column K of the "All Remotely Booked Txn Table" tab are within the date range. So we have 2 criteria now, the ID (which you helped already) and the date range.

 

[rangequestion]

Hi - any help with the above?

 

[mumps]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, sDate As Date, eDate As Date
    sDate = Sheets("Sheet1").Range("D12").Value
    eDate = Sheets("Sheet1").Range("D13").Value
    LastRow = Sheets("All Remotely Booked Txn Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("All Remotely Booked Txn Table").Range("K1:K" & LastRow).AutoFilter Field:=1, Criteria1:="<" & sDate, Operator:=xlOr, Criteria2:=">" & eDate
    Sheets("All Remotely Booked Txn Table").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If Sheets("All Remotely Booked Txn Table").AutoFilterMode Then Sheets("All Remotely Booked Txn Table").AutoFilterMode = False
    Sheets("All Remotely Booked Txn Table").Range("A1:L" & LastRow).AutoFilter Field:=2, Criteria1:="<>5"
    Sheets("All Remotely Booked Txn Table").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If Sheets("All Remotely Booked Txn Table").AutoFilterMode Then Sheets("All Remotely Booked Txn Table").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[rangequestion]

I'm getting an error on the 4th to last row that ends in "<>5"

The error reads "Run-time error '1004' Autofilter method of Range class failed"

I then went to the "All Remotely Booked Txn Table" tab and all the data was deleted instead of keeping the ID and specified date range.

 

[mumps]

Click here to download a sample file.
If you look at the "All Remotely Booked Txn Table" sheet, I've highlighted the rows which meet the criteria of being equal to 5 and between the 2 dates in D12 and D13 of "Sheet1". When you run the macro in Module1, the yellow rows will be the only ones selected.

 

[rangequestion]

I see how it works for you, very nifty. But still not working for me. My active sheet is named "Static Data" and I renamed Sheet1 to that. Didn't solve anything...Could something else be causing this?