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

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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
 
Upvote 0
Hi Mark,

Thanks for sharing another option . it worked. ?

Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top