how to filter the "others" in a VBA code

Question

I have created a macro that filters/countIF keywords and count their numbers for my report which is working fine. The help that I need is how can I filter the cells that are considered as "others" on my code. As shown on the image below highlighted in red, there are a lot of items that were counted as "others". I need to find out which cell they are so that I could amend my macro to find those items.

Public Sub Testing()

Dim row_number As Long
Dim count_of_Harmo As Long
Dim count_of_Room As Long
Dim count_of_Skyp As Long
Dim count_of_others As Long


Dim items As Variant
Dim cursht As String 'for the macro to run in any sheet
cursht = ActiveSheet.Name 'for the macro to run in any sheet

row_number = 1
count_of_Harmo = 0
count_of_Room = 0
count_of_Skyp = 0
count_of_others = 0


Do

row_number = row_number + 1
items = Sheets(cursht).Range("N" & row_number)

    If InStr(items, "harmo") Then
        count_of_Harmo = count_of_Harmo + 1
    ElseIf InStr(items, "room") Then
        count_of_Room = count_of_Room + 1
    ElseIf InStr(items, "skyp") Or InStr(items, "meeting") Then
        count_of_Skyp = count_of_Skyp + 1
    ElseIf items <> "" Then
        count_of_others = count_of_others + 1
    End If


Loop Until items = ""


Range("N2").Select


Selection.End(xlDown).Select
lastCell = ActiveCell.Address

ActiveCell.Offset(3, 1).Value = "Count"
ActiveCell.Offset(4, 1).Value = count_of_Harmo
ActiveCell.Offset(5, 1).Value = count_of_Room
ActiveCell.Offset(6, 1).Value = count_of_Skyp
ActiveCell.Offset(7, 1).Value = count_of_others

ActiveCell.Offset(3, 0).Value = "Add-ins breakdown"
ActiveCell.Offset(4, 0).Value = "HarmonIE"
ActiveCell.Offset(5, 0).Value = "Room Finder"
ActiveCell.Offset(6, 0).Value = "Skype"
ActiveCell.Offset(7, 0).Value = "Others"


End Sub

possible duplicate of [Excel VBA autofilter all but three](https://stackoverflow.com/questions/19497659/excel-vba-autofilter-all-but-three/35120033?s=8|19.2368#35120033) — , Jan 16 '18 at 05:16
Run through your list, and check if the value of that cell has any of the ones you want to keep (`INSTR()` probably would help), and if not, hide row/filter? — BruceWayne, Jan 16 '18 at 05:24

score 2 · Accepted Answer · edited Apr 03 '19 at 03:53

There are two ways you could go about doing this. The first, and personally the one I would choose, would be to create a new column at the end of your sheet to add a category. This would look like this:

If InStr(items, "harmo") Then
    count_of_Harmo = count_of_Harmo + 1
    Range("Q" & row_number).Value ="Harmon"
ElseIf InStr(items, "room") Then
    count_of_Room = count_of_Room + 1
    Range("Q" & row_number).Value ="Room Finder"
ElseIf InStr(items, "skyp") Or InStr(items, "meeting") Then
    count_of_Skyp = count_of_Skyp + 1
    Range("Q" & row_number).Value ="Skype"
ElseIf items <> "" Then
    count_of_others = count_of_others + 1
    Range("Q" & row_number).Value ="Other"
End If

Of course, you would make the column whatever is open. Then when the macro is done running, you can filter by that column to find all of the Others in your spreadsheet.

The other way, rather than adding the category, would be to hide rows if they were anything other than Other.

score 1 · Answer 2 · answered Jan 16 '18 at 06:22

It would be better to use the countif function.

Sub test()
    Dim rngDB As Range, rngT As Range
    Dim vR(1 To 5, 1 To 2)

    Set rngDB = Range("n2", Range("n2").End(xlDown))
    Set rngT = Range("n2").End(xlDown)(4)

    With WorksheetFunction
        vR(1, 1) = "Add-ins breakdown"
        vR(2, 1) = "HarmonIE"
        vR(3, 1) = "Room Finder"
        vR(4, 1) = "Skype"
        vR(5, 1) = "Others"

        vR(1, 2) = "count"
        vR(2, 2) = .CountIf(rngDB, "*harmo*")
        vR(3, 2) = .CountIf(rngDB, "*room*")
        vR(4, 2) = .CountIf(rngDB, "*skyp*") + .CountIf(rngDB, "*meeting*") - .CountIf(rngDB, "*skyp*meeting*")
        vR(5, 2) = .CountA(rngDB) - vR(2, 2) - vR(3, 2) - vR(4, 2)
    End With
    rngT.CurrentRegion.Clear
    rngT.Resize(5, 2) = vR
End Sub

how to filter the "others" in a VBA code

2 Answers2