itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Count and check empty cells of filtered columns using a macro in an excel report

I use this macro to check if there are blank cells after I filtered another column to do some actions. It can be also used to count them instead to check.

excel macro excel macro excel macro

 

When I use the macro ?

To check if there are empty cells after doing a filter.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.


Sub test()
' change A by your filtered column and xx by the filtered value
' change B by the column where to check empty cells
If Application.WorksheetFunction.CountIfs(Range("A:A"), "xx", Range("B:B"), "") > 0 Then
  Msgbox "yes empty cells"
Else
  Msgbox "no empty cells"
End If
End Sub      
' NOTE: if you want to count/know how many just put this code -> Msgbox Application.WorksheetFunction.CountIfs(Range("A:A"), "xx", Range("B:B"), "")

Interesting Topics