VBA Excel if column doesn't have a specific value

Question

I am trying to prevent a subroutine from running if the column "L" it's working with doesn't have any value "Yes"

    If Range("L2:L").WorksheetFunction.CountIf(col, "Yes") > 0 Then
    Call DeleteSubparts
    End If

There's a problem with my if function and I'm not sure what am I writing wrong. Could someone, please, help me?

What is col? Where do you define it? Have you looked at the official documentation for the WorksheetFunction method or the CountIf method? — Chronocidal
– Chronocidal, Commented Apr 19, 2021 at 10:37

Damian · Accepted Answer · 2021-04-19 11:26:59Z

1

I believe you want to do this:

If WorksheetFunction.CountIf(Range("L2:L100"), "Yes") > 0 Then
    DeleteSubparts
End If

The range goes inside the function, thought you should reference both the workbook and worksheet like this:

If WorksheetFunction.CountIf(Workbooks("WorkbookName").Worksheets("WorksheetName").Range("L2:L100"), "Yes") > 0 Then
    DeleteSubparts
End If

edited Apr 19, 2021 at 11:26

answered Apr 19, 2021 at 10:45

Damian

5,1741 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eduards Over a year ago

The syntax seems right, but it doesn't count what I need. It still executes the subroutine I am calling when what I'm trying to do is to not call deletesubparts if column "L" has no value "Yes"

Damian Over a year ago

@Eduards the syntax should count the "Yes" in that column, maybe is the reference. If you don't reference both workbook & worksheet it will count on the active sheet which could be totally a different sheet that the one you intend.

FunThomas Over a year ago

Hm. Either you write Range("L:L") or something like Range("L2:L" & lastRow)

Collectives™ on Stack Overflow

VBA Excel if column doesn't have a specific value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related