62704

How to Delete Rows Based on Criteria from 2 Columns in VBA?

Question:

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 With

Answer1:

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

Recommend

  • XPath expression with white space in node for defiant.js
  • how to sort the field in the mongo document which is inside array
  • Swift: Validate Username Input
  • $.ajax & passing data to .asmx webservice
  • Wordpress plugin for dependent drop down lists
  • sql for calculating points for games
  • Nhibernate QueryOver Orderby
  • Oracle: Using CTE with update clause
  • JPA - getting distinct value from one column
  • php regex remove digits
  • Add delivery info to query in SAP Crystal Reports
  • Update a record where _id = :id with Mongoose
  • How Get arguments value using inline assembly in C without Glibc?
  • How to make R's read_csv2() recognise the text characters properly
  • Ember.js model to be organised as a tree structure
  • How to disable all widgets inside Panel or inside Composite?
  • Implementation of State Monad
  • SharedPreferences or SQLite Database?
  • Groovy: Unexpected token “:”
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Replace value with Factor in r data.table
  • Extracting HTML between tags
  • MongoDB in PHP using aggregate to group by _id is null not working
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Adding a button at the bottom of a table view
  • Android screen density dpi vs ppi
  • Getting last autonumber in access
  • C# - Serializing and deserializing static member
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • swift auto completion not working in Xcode6-Beta
  • Bug in WPF DataGrid
  • Sending data from AppleScript to FileMaker records
  • Jquery - Jquery Wysiwyg return html as a string
  • Delete MySQLi record without showing the id in the URL
  • GridView Sorting works once only
  • How to include full .NET prerequisite for Wix Burn installer
  • Is it possible to post an object from jquery to bottle.py?
  • java string with new operator and a literal