CountA: Giving wrong output in VBA

Question

I am writing a VBA code in which I am trying to find if my range has empty values. The code I am writing is giving me True value even in case of empty ranges. I am adding the screen shots of data and code

    Range("A1").Select
    Dim last_row As Long
    Dim LastCol As Integer

    total_rows = Cells(Rows.Count, 1).End(xlUp).Row
    net_rows = total_rows - 1
    
    LastCol = ActiveSheet.UsedRange.Columns.Count
    
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "Starting Year"
    
    For each_row = 1 To net_rows Step 1
        If WorksheetFunction.CountA(Range(Cells(each_row + 1, 2), Cells(each_row + 1, LastCol)).Select) = 0 Then
            MsgBox "True"
        Else
            MsgBox "False"
        End If
    Next
         
End Sub

In above case it should True in two cases and False in rest of cases. But it is giving False in all cases. Range is is between column B and H. If could tell my error, it would be highly helpful

Answer 1

Drop the .Select after the range in CountA() by @Tim Williams and @Variatus

Answer 2

CountA vs CountBlank

Tips

• Read here about how to avoid using Select.
• Use Option Explicit, it will force you to declare all variables.
• Use objects like I did use a range object (rng) to make the code more readable (understandable).
• Think about this CountA/CountBlank 'business' for a while. You might not need it in this code, but it might become handy in the future.

The Code

Option Explicit

Sub test()

    Range("A1").Select
    Dim rng As Range
    Dim last_row As Long
    Dim LastCol As Long
    Dim total_rows As Long
    Dim net_rows As Long
    Dim each_row As Long
    
    total_rows = Cells(Rows.Count, 1).End(xlUp).Row
    net_rows = total_rows - 1
    
    LastCol = ActiveSheet.UsedRange.Columns.Count
    
    Range("A1").End(xlToRight).Offset(0, 1).Value = "Starting Year"
    
    For each_row = 1 To net_rows Step 1
        Set rng = Range(Cells(each_row + 1, 2), Cells(each_row + 1, LastCol))
        ' The problem with CountA is that it will find cells containing
        ' values evaluating to "".
        'If WorksheetFunction.CountA(rng) = 0 Then
        ' Therefore it is better to use CountBlank.
        If WorksheetFunction.CountBlank(rng) = rng.Columns.Count Then
            MsgBox "True"
        Else
            MsgBox "False"
        End If
    Next
         
End Sub