Forum Discussion

Copper Contributor

Oct 30, 2023

Solved

Count Black Cells In a Range Using VBA

I am trying to count blank cells in a range using VBA. Below is an abbreviated version of my code. The full macro contains more, but I figured all that is irrelevant at this point since it chokes o...

Oct 31, 2023

Try

    My_Tasks = WorksheetFunction.CountBlank(Range("Current_Tasks"))

HansVogelaar

MVP

Oct 30, 2023

mtthomasm516

In the code as posted, you don't set the variable Current_Tasks to a specific range.

mtthomasm516
Copper Contributor
Oct 31, 2023
HansVogelaar
Once upon a time I was incredibly good with Excel VBA, but that was way back in the Excel 4 days! Haven't done a lot of it since then, but still, I should have seen that! Current_Tasks is a named range in the worksheet. I guess I was just thinking that the macro would somehow know that!
- HansVogelaar
  MVP
  Oct 31, 2023
  mtthomasm516
  
  Try
  
  My_Tasks = WorksheetFunction.CountBlank(Range("Current_Tasks"))
  - mtthomasm516
    Copper Contributor
    Oct 31, 2023
    HansVogelaar
    Thanks! I got it working with:
    Set My_Tasks = Application.Range("B5:B9")
    Normally, I don't like hard coding ranges, but in this particular case, that range will never change, so it works well enough. Even so, I will see if I can get it working with the named range.