如何保留含指定数字单元格所在行并删除其余所有行

zymilan21

如何保留含指定数字单元格所在行并删除其余所有行？如B列单元格含数字5的行，整行保留，然后删除B列所有单元格不包含数字5所在的行，谢谢！

z_colin

for i = thisworkbook.activesheet.range("B1000000").end(xlup).row to 1 step -1
    if instr(1,thisworkbook.activesheet.cells(i,"B"),"5") = 0 then
        thisworkbook.activesheet.rows(i).delete
    end if
next i

凌空一羽

Sub lkyy()
Dim r As Range
ar = Range("a1").CurrentRegion
For i = 2 To UBound(ar)
    If InStr(ar(i, 2), 5) = 0 Then Cells(i, 2).ClearContents
Next

Range("b1:b" & UBound(ar)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

zymilan21

z_colin 发表于 2020-1-20 10:28
for i = thisworkbook.activesheet.range("B1000000").end(xlup).row to 1 step -1
    if instr(1,thiswo ...

直接运行会报错，如果改为B10000，运行后没有任何反应

zymilan21

凌空一羽 发表于 2020-1-20 10:42
Sub lkyy()
Dim r As Range
ar = Range("a1").CurrentRegion

非常感谢！