源数据表:Data check - Service Request Detail required fields all(SL).xlsx 源数据表B列=PUMA时 删除 A列包含FW的数据 源数据表B列=ASICS CORPORATION时 删除 R列(Market Segment Name) 包含RS的数据 源数据表B列=TOMS COMMERCE (SHANGHAI) CO.,LTD时 删除 AD列 (Service Type Detail)不为Finished Product的数据【相当于只保留】 源数据表删除AJ列(Service End)不含:“2022”“ 2023”的数据【相当于只保留】 源数据表删除D整列(Reference Number)
时间: 2024-03-16 10:42:38 浏览: 15
以下是VBA代码,可以实现你的需求:
```
Sub DataProcessing()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") '将“Sheet1”替换为你的源数据表名
'删除B列为“PUMA”且A列包含“FW”的数据
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim i As Long
For i = lastRow To 2 Step -1
If ws.Range("B" & i).Value = "PUMA" And InStr(1, ws.Range("A" & i).Value, "FW") > 0 Then
ws.Rows(i).Delete
End If
Next i
'删除B列为“ASICS CORPORATION”且R列包含“RS”的数据
lastRow = ws.Cells(ws.Rows.Count, "R").End(xlUp).Row
For i = lastRow To 2 Step -1
If ws.Range("B" & i).Value = "ASICS CORPORATION" And InStr(1, ws.Range("R" & i).Value, "RS") > 0 Then
ws.Rows(i).Delete
End If
Next i
'删除B列为“TOMS COMMERCE (SHANGHAI) CO.,LTD”且AD列不为“Finished Product”的数据
lastRow = ws.Cells(ws.Rows.Count, "AD").End(xlUp).Row
For i = lastRow To 2 Step -1
If ws.Range("B" & i).Value = "TOMS COMMERCE (SHANGHAI) CO.,LTD" And ws.Range("AD" & i).Value <> "Finished Product" Then
ws.Rows(i).Delete
End If
Next i
'删除AJ列不包含“2022”或“2023”的数据
lastRow = ws.Cells(ws.Rows.Count, "AJ").End(xlUp).Row
For i = lastRow To 2 Step -1
If InStr(1, ws.Range("AJ" & i).Value, "2022") = 0 And InStr(1, ws.Range("AJ" & i).Value, "2023") = 0 Then
ws.Rows(i).Delete
End If
Next i
'删除D列
ws.Columns("D").Delete
End Sub
```
请确保在运行代码之前备份你的源数据表,以防意外情况。