VBA 如果最后两个字符等于“XX”，则删除整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14246538/
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
VBA If Last Two Characters equals "XX" Then Delete Entire Row
提问by kmiao91
I am trying to run a macro where it looks at a string and determines whether the last two characters equal "XX", then it is to delete the entire row.
我试图运行一个宏，它查看一个字符串并确定最后两个字符是否等于“XX”，然后删除整行。
I am getting a object error oh the highlighted below.
我收到一个对象错误哦，下面突出显示。
Sub Oval2_Click()
Last = Cells(Rows.Count, "E").End(xlUp).Row
For i = Last To 1 Step -1
`If (Right(Cells(i, "E"), 2).Value) = "TZ" Then`
Cells(i, "E").EntireRow.Delete
End If
Next i
End Sub
Please help! Thank you.
请帮忙！谢谢你。
回答by StoriKnow
Remove .Valuefrom Right(Cells(i, "E"), 2).Value. It's complaining because .Valuedoesn't exist on the functionRight
删除.Value从Right(Cells(i, "E"), 2).Value。它在抱怨，因为函数.Value中不存在Right
回答by Siddharth Rout
Though you already have your answer but I wouldn't recommend looping to achieve this. Looping will be slow if you have large number of rows. Here is an alternative which is comparatively very fast. I have also commented the code so that you will not have a problem understanding it...
虽然你已经有了答案，但我不建议循环来实现这一点。如果您有大量行，循环会很慢。这是一个相对非常快的替代方案。我还对代码进行了注释，这样您就不会在理解它时遇到问题...
Logic:
逻辑：
- Use Autofilter to Filter data which ends in "XX"
- Delete filtered data
- 使用自动过滤器过滤以“XX”结尾的数据
- 删除过滤数据
Code:
代码：
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
'~~> Change this to the respective sheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Remove any filters
.AutoFilterMode = False
'~~> Get last row of Col A
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
'~~> Use autofilter to identify the cells which end in "XX"
With .Range("A1:A" & lRow)
.AutoFilter Field:=1, Criteria1:="=*XX"
'~~> Offset to exclude the header and delete them
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
'~~> Remove any filters
.AutoFilterMode = False
End With
End Sub
Screenshot:
截图：
回答by assylias
This
这个
If (Right(Cells(i, "E"), 2).Value) = "TZ" Then
Should probably be
应该是
If Right(Cells(i, "E").Value, 2) = "TZ" Then
This should work too:
这也应该有效：
If Right(Cells(i, "E"), 2) = "TZ" Then
