VBA Help - .SpecialCells(xlConstants, xlLogical).EntireRow.Delete

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I want to delete all rows where cells value <> "Keep" and <> "#N/A"
20000 rows want to delete. I used autofilter, loop, it worked,

Trying to learn new and multiple ways ....

Below code I got from this forum. its very fast
How to fit my requirement into below logic and delete entire row.


VBA Code:
Sub Cobalt()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("superseded", "inactive", "defunct")
   
   For i = 0 To UBound(Ary)
      Range("K:K").Replace Ary(i) & "*", True, xlWhole, , False, , False, False
   Next i
   Range("K:K").SpecialCells(xlConstants, xlLogical).EntireRow.Delete
End Sub


Below is dummy Column A.
Book5
AB
1Data
2Overduedelete
3Keep
4Overduedelete
5#N/A
6#N/A
7#N/A
8#N/A
9Keep
10Overduedelete
11#N/A
12Paiddelete
13Keep
14#N/A
15#N/A
16Keep
17Pendingdelete
18Keep
19Keep
20#N/A
Sheet1


Thanks
mg
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming there are no formulas in column A, try this:
VBA Code:
Sub DeleteIf()
Dim V As Variant, i As Long
With Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    V = .Value
    For i = 1 To UBound(V, 1)
        If IsError(V(i, 1)) Then GoTo Nx
        If V(i, 1) <> "Keep" Then V(i, 1) = True
Nx: Next i
    .Value = V
    Application.ScreenUpdating = False
    On Error Resume Next
        Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
    Application.ScreenUpdating = True
End With
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi JoeMo,

Perfect ! it worked. thank. (y)


Thanks
mg
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Trying to learn new and multiple ways ....
Seeing as you are just trying to learn new ways, another method below (should be slower than the code JoeMo posted).

VBA Code:
Sub delNotarray()

    Dim rFnd As Range, rDel As Range
    Dim sAddr As String, vList As Variant, lCnt As Long

    Application.ScreenUpdating = False
   
    vList = VBA.Array("Keep", "#N/A")
   
    For lCnt = LBound(vList) To UBound(vList)
   
        With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
            Set rFnd = .Find(vList(lCnt), , xlValues, xlWhole, xlByRows, xlNext, False)
           
            If Not rFnd Is Nothing Then
                If rDel Is Nothing Then
                    Set rDel = rFnd
                Else
                    Set rDel = Application.Union(rDel, rFnd)
                End If
               
                sAddr = rFnd.Address
                Set rFnd = .FindNext(After:=rFnd)
               
                Do Until rFnd.Address = sAddr
                    Set rDel = Application.Union(rDel, rFnd)
                    Set rFnd = .FindNext(After:=rFnd)
                Loop
            End If
        End With
    Next lCnt
    Columns(1).Insert
   
    If Not rDel Is Nothing Then rDel.Offset(, -1) = "xxx"
    Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(4).EntireRow.Delete
    Columns(1).Delete
    Application.ScreenUpdating = True

End Sub
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi Mark,

Thanks for sharing another option . it worked. 🕺

Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top