vba 根据字符长度删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24701649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting rows based on character length
提问by connor
trying to delete rows with cells with fewer than 2 characters. range("A1") line is highlighted and i have no idea why.
i can run it without the line and for some reason it deletes everything.
any advice greatly appreciated. here's the code:
尝试删除单元格少于 2 个字符的行。range("A1") 行被突出显示，我不知道为什么。我可以在没有线路的情况下运行它，并且由于某种原因它会删除所有内容。
非常感谢任何建议。这是代码：
Option Explicit
Sub way()
Dim cell As Range
Range(“A1").CurrentRegion.activate
For Each cell In Selection
If Len(cell) < 2 Then Selection.EntireRow.Delete
Next cell
End Sub
回答by IAmDranged
Give this a try
试试这个
Sub mysub()
Dim r As Range
Dim i As Double
Dim rcount as Double
Dim mybool As Boolean
Set r = Range("A1").CurrentRegion
i = 1
mybool = False
Do
rcount = r.Rows.count
For j = 1 To r.Columns.count
If Len(Cells(i, j).Value) < 2 Then
Rows(i).Delete
If rcount = 1 then Exit Sub
mybool = True
Exit For
End If
Next j
If mybool = False Then i = i + 1
mybool = False
Loop While i <= rcount
End Sub
Edit: just to elaborate on why I provided a new code alltogether here - the logic behind the original code is actually flawed anyway.
编辑：只是为了详细说明为什么我在这里提供了一个新代码 - 无论如何，原始代码背后的逻辑实际上是有缺陷的。
Consider for instance what happens if you range involves the following consecutive rows
例如，考虑如果范围涉及以下连续行会发生什么
A B C D E
1 ee e eee ee eee
2 f fff fff ff ff
Your code will explore each cell row by row top to bottom, from left to right. So in this example:
您的代码将从上到下，从左到右逐行探索每个单元格。所以在这个例子中：
- when reaching B1, it will delete row 1, and row 2 will be moved to row 1
- from there, your loop will pick up from cell C1 - not A1. In other words, it will miss out on exploring the value of cell A1 which should qualify the row for deletion
- 到达B1时删除第1行，第2行移到第1行
- 从那里，您的循环将从单元格 C1 - 而不是 A1 开始。换句话说，它将错过探索单元格 A1 的值，该值应该有资格删除行
回答by brettdj
You can avoid a slow loop by using AutoFilter
您可以通过使用避免慢循环 AutoFilter
This code
这段代码
- Works out the size of the current region from A1
- In the next column adds an array formula checking the length of all cells in each row,
=MIN(LEN(A1:C1))<2 - AutoFilter deletes the
Trueresults
- 从 A1 算出当前区域的大小
- 在下一列中添加一个数组公式，检查每行中所有单元格的长度，
=MIN(LEN(A1:C1))<2 - 自动筛选删除
True结果
code
代码
Sub NoLoops()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1").CurrentRegion
Set rng2 = Range(Cells(1, rng1.Columns.Count + 1), Cells(rng1.Rows.Count, rng1.Columns.Count + 1))
ActiveSheet.AutoFilterMode = False
With rng2
.Formula = "=MIN(LEN(RC[-" & rng1.Columns.Count & "]:RC[-1]))<2"
.FormulaArray = .FormulaR1C1
.Value = .Value
.AutoFilter Field:=1, Criteria1:="TRUE"
.EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub
回答by Socii
@IAmDranged is correct in that when you are deleting a row, the next row will move up and become the current row. The Next cellline will then pass over this row and move to the next row without checking to see if any Cells are less than 2 characters in length.
@IAmDranged 是正确的，因为当您删除一行时，下一行将向上移动并成为当前行。所述Next cell然后线将经过该行并移动到下一行，而不检查，以查看是否任何细胞是长度小于2个字符。
Another method for this would be to leave the Deletemethod until after the Cells with fewer than 2 characters have been found:
另一种方法是离开该Delete方法，直到找到少于 2 个字符的单元格之后：
Sub way()
Dim cell As Range
Dim deleteRange As Range 'This will be used to store the Cells found
Range("A1").CurrentRegion.Activate
For Each cell In Selection
If Len(cell) < 2 Then
If deleteRange Is Nothing Then
' If this is the first cell found, then Set deleteRange to this cell
Set deleteRange = cell
Else
' Any cells found after the first, we can use the
' Union method to add it to the deleteRange
Set deleteRange = Application.Union(cell, deleteRange)
End If
End If
Next cell
' Once all cells have been found, then Delete
deleteRange.Delete
End Sub
回答by Tinbendr
Sub way()
Dim Cell As Range
For Each Cell In Range("A1").CurrentRegion
If Len(Cell) < 2 Then Cell.EntireRow.Delete
Next Cell
End Sub
