在Excel中，如何编写循环，将不断删除单元格的内容，除非满足条件？ [英] In Excel, how can I write loop that will keep deleting the contents of a cell unless a condition is met?

问题描述

因此，我有三个非常大的数据列。我希望这些匹配，但列之间有很多不匹配的行。

我想做的是写一个循环宏来删除单元格 F2 中的内容，如果它们不相等到 A2 或 K2 中的内容。但是，我只能找到有关编写循环宏的范围的细节。是否有可能在同一个单元上执行一个命令？到目前为止，我有：

  Sub ArrayMatch（）
 Application.ScreenUpdating = True 
 Dim F As Range 
对于每个F范围（F2：F2043）。单元格
 F.Select 
如果ActiveCell<> ActiveCell.Offset（[0]，[-5]）和ActiveCell<> ActiveCell.Offset（[0]，[5]）然后
 Selection.Delete Shift：= xlUp 
否则：停止
结束如果
下一个
  

目前，我只是希望代码停止，如果这些都是相等的。但是，我在这里定义范围的方式，代码只适用于范围内的每个其他单元格。我可以重新表述这个范围，让其余代码一次又一次地应用于单元格 F2

谢谢！

解决方案

假设您的输入：

我可以重新表达这个范围，让其余的代码应用到单元格
F2一遍又一遍吗？

这不是你期望的。线索是您应该检查范围内的每个单元格，只有在不符合标准的情况下才能移动到NEXT。否则行被删除，你应该保持在同一个地方，即不要向下移动，因为如果 A1 被删除， A2 现在成为 A1 ，您应该再次检查。

以下代码将工作（也许你应该修改标准，但是想法是这样）：

  Sub RemoveRows（）
 
 Dim i As Long 
 Dim ActiveCell As Range 
 
i = 2 
 
尽管我<= 2043 
设置ActiveCell =范围（F & i）
如果ActiveCell<> ActiveCell.Offset（[0]，[-5]）和ActiveCell<> ActiveCell.Offset（[0]，[5]）然后
 Selection.Delete Shift：= xlUp 
 Else 
i = i + 1 
如果
 
循环
 
 End Sub 
  

这是非常相似的任务的示例：

So, I have three very large columns of data. I want these to match, but there are lots of mismatching rows between the columns.

What I want to do is write a looping macro to delete the contents in cell F2 if they are not equal to the contents in either A2 or K2. However, I can only find details on writing looping macros for ranges. Is it possible to have a command carried out on the same cell over and over? So far I have:

Sub ArrayMatch()
Application.ScreenUpdating = True
Dim F As Range
For Each F In Range("F2:F2043").Cells
 F.Select
 If ActiveCell <> ActiveCell.Offset([0], [-5]) And ActiveCell <> ActiveCell.Offset([0],        [5]) Then
 Selection.Delete Shift:=xlUp      
  Else: Stop
  End If
  Next

At the moment, I just want the code to stop if any of these are equal. However, the way I have the range defined here, the code is only applied to every other cell in the range. Can I rephrase this range to have the rest of the code applied to cell F2 over and over again?

Thanks! I'll keep experimenting with what I have while eagerly awaiting a response!

解决方案

Assuming your input:

Can I rephrase this range to have the rest of the code applied to cell F2 over and over again?

that's NOT exactly what you expect. The clue is you should check every cell in range, and move to the NEXT only in case it does NOT meet the criteria. Otherwise the row is deleted, and you should stay on the same spot, i.e. DON'T move down, since if A1 is removed, A2 now becomes A1, and you should check it again.

The below code will do the job (perhaps you should modify the criteria, but the idea is that):

Sub RemoveRows()

Dim i As Long
Dim ActiveCell As Range

i = 2

Do While i <= 2043
    Set ActiveCell = Range("F" & i)
    If ActiveCell <> ActiveCell.Offset([0], [-5]) And ActiveCell <> ActiveCell.Offset([0], [5]) Then
        Selection.Delete Shift:=xlUp
    Else
        i = i + 1
    End If

Loop

End Sub

This is the sample for quite similar task: https://www.dropbox.com/s/yp2cwphhhdn3l98/RemoweRows210.xlsm

这篇关于在Excel中，如何编写循环，将不断删除单元格的内容，除非满足条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！