如何从VBA中的2列中基于条件删除行? [英] How to Delete Rows Based on Criteria from 2 Columns in VBA?
问题描述
我需要清理一些记录,方法是删除与我们无关的特定位置的行.我想弄清楚如何编写VBA代码,该代码将搜索目标城市"和目标州"列(在我当前的情况下为L& M列),并删除与特定城市和州匹配的记录.
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
推荐答案
数据如下:
我们想删除加利福尼亚州斯普林菲尔德的所有行,但保留佛罗里达州斯普林菲尔德.
We want to remove all rows for Springfield, California but keep Springfield, Florida.
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
这篇关于如何从VBA中的2列中基于条件删除行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!