Using CountIf in vba

DavidSCowan · July 31st, 2009, 09:41 AM

Hi

Could someone help please. I am new to VBA and this is my first post.

I am trying simply to delete a column if a particular cell in that column contains the word "Total". I thought this would be easy!

Sub RemoveColumns()

finalcolumn = Cells(4, Columns.Count).End(xlToLeft).Column

For k = finalcolumn To 2 Step -1

If Application.WorksheetFunction.CountIf(Cells(4, k).Value, "*Total*") = 1 Then
Cells(1, k).EntireColumn.Delete
End If

Next k
End Sub

When I run this I get "Run Time Error 424 Object Required".

What am I doing wrong?

Shasur · July 31st, 2009, 09:51 PM

Hi

Welcome to Wrox!

The error occurs as Countif expects a Range and you have given the Value. The following code should work

Code:

Sub RemoveColumns_CountIf()
Dim finalcolumn
Dim k
finalcolumn = Cells(4, Columns.Count).End(xlToLeft).Column
    For k = finalcolumn To 2 Step -1
    
       If Application.WorksheetFunction.CountIf(Cells(4, k), "*Total*") = 1 Then
            Cells(1, k).EntireColumn.Delete
        End If
        
    Next k
End Sub

Since you are checking each cell for a value, you can do it using VBA's Instr function. Here is a sample of that

Code:

Sub RemoveColumns_Instr()
Dim finalcolumn
Dim k
finalcolumn = Cells(4, Columns.Count).End(xlToLeft).Column
    For k = finalcolumn To 2 Step -1
    
       If InStr(1, Cells(4, k).Value, "Total", vbTextCompare) = 1 Then
            Cells(1, k).EntireColumn.Delete
        End If
        
    Next k
End Sub

Cheers
Shasur

DavidSCowan · August 1st, 2009, 07:51 AM

Hi Shasur

Thank you very much for this. I was being a bit silly I am afraid not to notice that a range not a value was required. Also thank you for introducing me to InStr.

Could I ask a supplementary?

I have more than one criterion for deleting the column and have the following code:

Sub RemoveColumns()
finalcolumn = Cells(5, Columns.Count).End(xlToLeft).Column
For k = finalcolumn To 1 Step -1

If Application.WorksheetFunction.CountIf(Cells(5, k), "*Total*") = 1 Then
Cells(1, k).EntireColumn.Delete
End If

If Application.WorksheetFunction.CountIf(Cells(5, k), "*52 Weekly*") = 1 Then
Cells(1, k).EntireColumn.Delete
End If

Next k

End Sub

Is there a more elegant way of writing this? I feel I ought to be able to use With End-With but don't see how to use it in this case.

Thanks again

David

Shasur · August 1st, 2009, 09:20 PM

You can tweak your code a bit like:

Code:

Sub RemoveColumns()
Dim finalcolumn, k
finalcolumn = Cells(5, Columns.Count).End(xlToLeft).Column
For k = finalcolumn To 1 Step -1
    If Application.WorksheetFunction.CountIf(Cells(5, k), "*Total*") = 1 Or Application.WorksheetFunction.CountIf(Cells(5, k), "*52 Weekly*") = 1 Then
        Cells(1, k).EntireColumn.Delete
    End If
    
Next k
End Sub

Select case might be helpful if you are checking against the entire contents of the cell.

Cheers
Shasur

DavidSCowan · August 2nd, 2009, 01:54 AM

Thank you - very helpful.

David

vnoancea · October 14th, 2014, 12:40 PM

Shure thing.

Thanks. Victor