Combine UCase and IsNumeric

[nniedzielski]

I am running this code to delete any row that shows Yes in column C, False in column M, Or the number 1 in column AH, when i run this, only the Yes and False are being found and deleted. The 1's remain. I am however, not getting any errors, it just doesnt seem to be finding them.

Dim nc As Long, i As Long, k As Long, lr As Long
    
    Set ws = Sheets("Data")
    With ws
      nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
      lr = .Columns("C:AH").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      a = Application.Index(.Cells, Evaluate("row(2:" & lr & ")"), Array(3, 14, 34))
      ReDim b(1 To UBound(a), 1 To 1)
      For i = 1 To UBound(a)
        If UCase(a(i, 1)) = "YES" Or UCase(a(i, 2)) = "FALSE" Or IsNumeric(a(i, 3)) And a(i, 3) = 1 Then
          b(i, 1) = 1
          k = k + 1
        End If
      Next i
      If k > 0 Then
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        With .Range("A2").Resize(UBound(a), nc)
          .Columns(nc).Value = b
          .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
          .Resize(k).EntireRow.Delete
        End With
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
      End If
    End With

 

[rpaulson]

here is an option: try this on a COPY of your file.

Sub DeleteRows() 'This macro will delete the entire row if there is a data match anywhere in the range.

On Error Resume Next
Dim lr as integer
lr = Columns("C:AH").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    With Range("C2:C"&lr)
        .Replace "Yes", False, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With

    With Range("M2:M"&lr)
        .Replace "False", False, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With

    With Range("AH2:AH"&lr)
        .Replace 1, False, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With

On Error GoTo 0
End Sub

 

[Fluff]

Your code works fine for me, is col AH a formula? If so what is the formula?

 

[nniedzielski]

Your code works fine for me, is col AH a formula? If so what is the formula?

its not a formula, its just 0's and 1's. I will try it again.

 

[Fluff]

Ok let us know it it goes.

 

[nniedzielski]

Ok let us know it it goes.

it was my mistake, i didnt take into account a column being added, so my macro was reading one column off

 

[Fluff]

Glad you sorted it & thanks for letting us know.