Deleting rows on false AND #VALUE!

[Lallo]

I have a sheet, where Column A has Boolean values. However there are also cells with #VALUE!. I want to delete the entire rows with FALSE or #VALUE! in column A.

I have the code below which does half the job, it deletes the FALSE rows. What I need now is to delete the #VALUE! rows, but I cannot make it work...

Sub KillRows_Input()
     
     
     
    Dim MyRange As Range, DelRange As Range, C As Range
    Dim MatchBoolean As Boolean, SearchColumn As String, ActiveColumn As String
    Dim FirstAddress As String, NullCheck As String
    Dim AC
     
    Application.ScreenUpdating = False
     
     'Extract active column as text
    AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
    ActiveColumn = AC(0)
     
    SearchColumn = "A"
     
    
    Set MyRange = Columns(SearchColumn)
    On Error GoTo 0
     
     'If an invalid range is entered then exit
    If MyRange Is Nothing Then Exit Sub
     
    MatchBoolean = False
    
        
     'to match the WHOLE text string
    Set C = MyRange.Find(What:=MatchBoolean, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole)
    
     
    If Not C Is Nothing Then
        Set DelRange = C
        FirstAddress = C.Address
        Do
            Set C = MyRange.FindNext(C)
            Set DelRange = Union(DelRange, C)
        Loop While FirstAddress <> C.Address
    End If
     
     'If there are valid matches then delete the rows
    If Not DelRange Is Nothing Then DelRange.EntireRow.Delete
    
         
End Sub

Any thoughts?

 

[tigeravatar]

Lallo,

Give the following a try:

Sub KillRows_Input()
    
    Static rngA As Range: Set rngA = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("A"))
    rngA.AutoFilter Field:=1, Criteria1:=False, Criteria2:="#Value!", Operator:=xlOr
    
    On Error Resume Next
    Dim rngDel As Range: Set rngDel = rngA.Offset(1).Resize(rngA.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlShiftUp
    ActiveSheet.AutoFilterMode = False
    
End Sub

Hope that helps,
~tigeravatar

 

[Lallo]

Lallo,

Give the following a try:

Sub KillRows_Input()
    
    Static rngA As Range: Set rngA = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("A"))
    rngA.AutoFilter Field:=1, Criteria1:=False, Criteria2:="#Value!", Operator:=xlOr
    
    On Error Resume Next
    Dim rngDel As Range: Set rngDel = rngA.Offset(1).Resize(rngA.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlShiftUp
    ActiveSheet.AutoFilterMode = False
    
End Sub

Hope that helps,
~tigeravatar

Works like a charm! Thanks alot!

/Lallo