CODEWITHSUNDEEP
excelvba
icalderon
icalderon

Reputation: 63

Filter table based on cell value/format

A slight modification is needed for the code below. This is based on a previous question I posted How to use If-Else statements to filter a table?

Column 14 is to be filtered and has values in Accounting number format. I would like to search Column 14 for any values that are NOT $- , i.e. $ 145,0450.56. If there is a cell that is $- then continue and filter.

I tried using <> as a way to say value that are NOT equal to $- but this doesn't work. 

Sub DeleteJob()

Dim tbl As ListObject
Dim ws As Worksheet

  'Set reference to the sheet and Table.
  Set ws = Sheets("Line Item Summary")
  Set tbl = ws.ListObjects("Table1")
  ws.Activate

  'Clear any existing filters
  tbl.AutoFilter.ShowAllData

    With tbl.ListColumns(14)
        If Application.CountIf(.DataBodyRange, ">0") + _
           Application.CountIf(.DataBodyRange, "<0") = 0 Then
             Exit Sub
        End If
    End With

  'Apply Filter
  tbl.Range.AutoFilter Field:=14, Criteria1:=""

  'Delete Rows
  Application.DisplayAlerts = False

    tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

  Application.DisplayAlerts = True

  '3Clear Filter
  tbl.AutoFilter.ShowAllData

End Sub

EDIT

This is the line I edited:
If Application.CountBlank(tbl.ListColumns(14).DataBodyRange) = 0 Then Exit Sub

When I run the code it ignores the non value rows (shown below) and exits the macro half way. The code needs to recognize that there are rows with $- and continue with the code to filter.

enter image description here

Upvotes: 0

Views: 394

Answers (1)

BigBen
BigBen

Reputation: 50162

$- is just 0, formatted.

One option is just to check if you have values not equal to zero:

With tbl.ListColumns(14)
    If Application.CountIf(.DataBodyRange,">0") + _
       Application.CountIf(.DataBodyRange,"<0") = 0 Then
         Exit Sub
    End If
End With

Note that Application.CountIf(.DataBodyRange,"<>0") would include blank cells, which is why you can use the "long route" as shown above.

EDIT: I think I got it backwards:

With tbl.ListColumns(14)
    If Application.CountIf(.DataBodyRange,"=0") = 0 Then
         Exit Sub
    End If
End With

Upvotes: 1

Related Questions