如果在其他工作表中的列中找不到单元格值，则Excel VBA删除行 [英] Excel VBA delete row if cell value not found in a column in other sheet

问题描述

我是VBA的新手，在这里处理了一个巨大的数据集。我试图摆脱不符合一个标准的观察。我需要在Sheet1（大约200,000行）中查看Column1的每个单元格，并检查单元格值是否在Sheet2（列中有大约3000行）中列出的接受值中。如果然后向前移动，如果不是，则Sheet1中的单元格的整行需要删除。

以下代码似乎无法正常工作，例如，它不会一次删除所有行，但必须运行多次，并且需要时间。我不知道Find方法是否正在做正确的工作。任何帮助将高度赞赏！

（Sheet1中Column1中有多个单元格具有相同的值，它们是根据值排序的，还可以通过删除来加快整个过程所有这些都是一次？）

  Sub DeleteRows 
'删除一个单元格不符合条件的行
 
 Dim ws1 As Worksheet：设置ws1 = ActiveWorkbook.Sheets（Sheet1）
 Dim ws2 As Worksheet：设置ws2 = ActiveWorkbook.Sheets（Sheet2）
 Dim criteria As String 
 Dim发现As Range 
 Dim i As Long 
 
 Application.ScreenUpdating = False 
 
对于i = 2至200000 
 criteria = ws1。单元格（i，1）.Value 
 On Error Resume Next 
 Set found = ws2.Range（A：A）。Find（What = = criteria，LookAt：= xlWhole）
 On Error GoTo 0 
 
如果发现Is Nothing Then 
 ws1.Cells（i，1）.EntireRow.Delete 
 End If 
 Next i 
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

解决方案

删除行时，您需要从底部到顶部，否则您将冒险丢失一些行（如您所见）。因此，您应该更换...

 对于i = 2至200000 
  

... with ...

  For i = 200000到2步-1 
  

...在你的代码，它应该按照预期工作。 / p>

I am new to VBA and dealing with a huge data set here. I am trying to get rid of observations that do not meet one criteria. I need to go through each cell of Column1 in Sheet1 (around 200,000 rows) and check if the cell value is among accepted values listed in Column1 in Sheet2 (there are some 3000+ rows there). If it is then move forward, if it's not then the whole row with the cell in Sheet1 needs to be deleted.

The code below does not seem to work properly, for example it does not delete all the rows at once, but has to be run several times, and takes ages. I am not sure if the Find method is doing the right job either. Any help would be highly appreciated!

(There are multiple cells in Column1 of Sheet1 that have the same value, and they are ordered according to value, is it also possible to speed up the whole process by deleting all of them at once?)

Sub DeleteRows
'Deletes rows where one cell does not meet criteria

Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2")
Dim criteria As String
Dim found As Range
Dim i As Long

Application.ScreenUpdating = False

 For i = 2 To 200000 
   criteria = ws1.Cells(i, 1).Value
   On Error Resume Next
   Set found = ws2.Range("A:A").Find(What:=criteria, LookAt:=xlWhole) 
   On Error GoTo 0

   If found Is Nothing Then
     ws1.Cells(i, 1).EntireRow.Delete
   End If
  Next i

Application.ScreenUpdating = True

End Sub

解决方案

When deleting rows you need to go from bottom to top otherwise you would risk missing some rows (as you have encountered). You should therefore replace...

For i = 2 To 200000 

...with...

For i = 200000 To 2 Step -1 

...in you code, and it should work as intended.

这篇关于如果在其他工作表中的列中找不到单元格值，则Excel VBA删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！