如何将A列所有单元格值是"AAA"的整行删除?

xhjok

如何将A列所有单元格值是"AAA"的整行删除?

如何将A列所有单元格值包含"AAA"的整行删除?

求以上代码,谢谢

extyg

Sub test()
    Cells.AutoFilter
    Cells.AutoFilter field:=1, Criteria1:="AAA"
    Range("a2:a" & [a65536].End(xlUp).Row).EntireRow.Delete
    Cells.AutoFilter
End Sub

maditate

QUOTE:

以下是引用lenghonghai在2007-9-18 15:04:50的发言：

sub xxx

dim x%,i%

x=range("a65536").end(xlup).row

for i=1 to x

if range("a" & i)="AAA" then

range(i & ":" & i).delete

end if

next i

end sub

穷举法，低效。如果两行相连就有漏删现象。

[此贴子已经被作者于2007-9-18 15:30:42编辑过]

daley

Private Sub CommandButton1_Click()
    Dim x As Integer
    For x = 1 To [A65536].End(xlUp).Row
    If InStr(Cells(x, 1).Value, "AAA") Then
    Rows(x).Delete
    End If
    Next x
End Sub

northwolves

可借用辅助列:

Sub MACRO1()
Dim n As Long
n = [a65536].End(xlUp).Row
With [iv1].Resize(n, 1)
.Formula = "=IF(RC1=""AAA"",1/0,1)"
.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
.Value = ""
End With
End Sub

Sub MACRO2()
Dim n As Long
n = [a65536].End(xlUp).Row
With [iv1].Resize(n, 1)
.Formula = "= IF(LEN(RC1)>LEN(SUBSTITUTE(RC1,""AAA"","""")),1/0,1)"
.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
.Value = ""
End With
End Sub

山菊花

从下往上删除：

Private Sub CommandButton1_Click()
    Application.DisplayAlerts = False
    Dim x As Integer, nRow%
    nRow = [A65536].End(xlUp).Row
    For x = nRow To 1 Step -1
    If Cells(x, 1).Value = "AAA" Then
    Rows(x).Delete
    End If
    Next x
    Application.DisplayAlerts = True
End Sub

maditate

试试用Match函数

Sub dfd()
Do
行号 = Application.Match("AAA", Range("A:A"), 0)
If IsNumeric(行号) = False Then Exit Do
Rows(行号).Delete
Loop
End Sub

yuanzhuping

Sub Del()
        Dim R As Integer
        R = Sheet1.[A65536].End(xlUp).Row
        With Range("A1:A" & R)
            .Replace What:="AAA", Replacement:="=A1"
            .SpecialCells(xlCellTypeFormulas).EntireRow.Delete
        End With
    End Sub

xhjok

感谢各位大侠

huiz999

QUOTE:

以下是引用yuanzhuping在2007-9-18 15:49:35的发言：

Sub Del()
        Dim R As Integer
        R = Sheet1.[A65536].End(xlUp).Row
        With Range("A1:A" & R)
            .Replace What:="AAA", Replacement:="=A1"
            .SpecialCells(xlCellTypeFormulas).EntireRow.Delete
        End With
    End Sub

这样会把A列其他含有公式的单元格所在的行也删除了！这个方法并不够严谨