Count Rows EntireColum one Shot in VBA CountA IsNumber, IsEmpty, Istext, HasFormulas

[xman2000]

Hi, Kenneth, thank you very mutch!

i am working in the button between blue colors, macro name "Sub LastRowLoopAREAS_IF3()".

your code is great, but if the column is entire empty, returns error!

beacuse this, i want other solution to count if exists one or more data of each type (numbers, formulas, text, etc) in the column before set the range.

Example:

Sub MainKennethXman1()
    Dim r As Range, rr As Range
     
     Set r = Range("A:A")
     
    If IsEmpty(r) Then
        
     Exit Sub
     Else
     
    Set r = Range("A:A").SpecialCells(xlCellTypeFormulas, xlNumbers)
    Set rr = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)

but need determine the type of data (number, text, etc) to not error occurs in the start of macro.
I'm trying this apt this:
in this link i found the below code "https://exceljet.net/formula/count-cells-that-contain-text"

=SUMPRODUCT(--ISTEXT(rng))

You can also use SUMPRODUCT to count text values along with the function ISTEXT like so:=SUMPRODUCT(--ISTEXT(rng))
The double hyphen (called a double unary) coerces the result of ISTEXT from a logical value of TRUE or FALSE, to 1's and 0's. SUMPRODUCT then sums these values together to get a result.

And use this.

    If Application.WorksheetFunction.IsText(Range("A1")) Then MsgBox "IsText" 
    If Application.WorksheetFunction.IsNumber(Range("A1")) Then MsgBox "IsNumber" 
     
    If IsNumeric(Range("A1")) Then MsgBox "IsNumeric"

Example:

Function test(var1 As Range, var2 As Range, rng1 As Range, rng2 As Range)
Dim strFormula As String
   strFormula = "SumProduct((" & rng1.Address & "=" & var1.Address & ") * 1,(" & rng2.Address & "<" & var2.Address & ") * 1)"
   test = Application.Evaluate(strFormula)
End Function

I know workarounds but i need know this way with Evaluate and Sumproduct, CountIf or Application.WorksheetFunction because are more powerful and versatile and fast.

thanks.