I need to clean up some records by removing rows with specific locations that we don't do business with. I would like to figure out how to write VBA code that would search through the Destination City and Destination State columns (columns L & M in my current case) and delete the records that match a specific city AND state.
I have figured out how to delete records by multiple criteria within the same column but not across columns.
With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With Sheets("data_export") .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False Firstrow = 2 Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For lRow = Lastrow To Firstrow Step -1 With .Cells(lRow, "D") 'Stops' If Not IsError(.Value) Then If .Value >= "1" Then .EntireRow.Delete End If End With With .Cells(lRow, "P") 'HaZMat If Not IsError(.Value) Then If .Value <> "" Then .EntireRow.Delete End If End With With .Cells(lRow, "T") 'Service If Not IsError(.Value) Then If .Value <> "Truck Load" Then .EntireRow.Delete End If End With Next lRow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End WithAnswer1:
With data like:
<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/u9oWZ.png" data-original="https://i.stack.imgur.com/u9oWZ.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />
We want to remove all rows for <em>Springfield, California</em> but keep <em>Springfield, Florida.</em>
Sub RowKiller() Dim N As Long, i As Long N = Cells(Rows.Count, "L").End(xlUp).Row For i = N To 1 Step -1 If Cells(i, "L") = "Springfield" And Cells(i, "M") = "California" Then Cells(i, "L").EntireRow.Delete End If Next i End Sub