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

[xman2000]

Hi, Mancubus, AussieBear, and Partners!

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

i am trying use CountA( ), Count(), CountIF(), and SpreadsheetFunctions in VBA editor to count Rows of EntireColumn in OneShot to
determine quantity of rows with this values, without looping, one shot.

my tests not works.

i am close the previous thread without solve all problems but solve the initial subjects keyowrds.

i am open new thread to solve other subjects keywords.

Only this subject not working in the sample file, the other all code of macro runs fine.


iam put here below the new sample file with new progress and working!

thank you very mutch!


in this link i found the below code "https://exceljet.net/formula/count-cells-that-contain-text"

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.

sample file below
RangeSpecialCells-Xman-IF3-working.zip
RangeSpecialCells-Xman-IF3-not-working.zip

[Kenneth Hobs]

You have so many modules and code, it hard to see where you want help.

Doing all on one line is bound to error.

If you count isempty for an entire column of rows, you may wind up with over a million. I don't see a purpose in that.

I don't like putting all my eggs into one basket. This shows how to quickly get and count the cells with a number value.

Sub Main()  
  Dim r As Range, rr As Range
  
  Set r = Range("A:A").SpecialCells(xlCellTypeFormulas, xlNumbers)
  Set rr = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
  
  If r Is Nothing Then
    Set r = rr
    Else
    Set r = Union(r, rr)
  End If
  
  If r Is Nothing Then Exit Sub
  
  MsgBox r.Cells.Count
End Sub

[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.

[xman2000]

"https://exceljet.net/formula/count-cells-that-contain-text"

Need put double hyphen (called a double unary) to Istext return 1's and 0's to makes sum.

=SUMPRODUCT(--ISTEXT(rng))

Dim strFormula As String
strFormula = "SumProduct(" & IsText & "(" & Columns(3).Address & "))"


MsgBox Application.Evaluate(strFormula)

This below works fine for numeric values but i not able to make this work with text
if i put the double hyphen returns type mismatch erros and others.

Dim strFormula As String
strFormula = "SumProduct(" & IsText & "(" & Columns(3).Address & "))"
MsgBox Application.Evaluate(strFormula)

I want count entire column in oneShot to know if exists and quantity of each data type to determine if set the range and run the macro or exit sub.

I am make plot of graphs using autoshapes and need only numeric values but it is the array to map the worksheet used range dinamicaly.

i know workarounds but this way with Evaluate, Sumproduct, CountA, and Worksheet.Functions() and IsEmpty, IsText, IsNumber, HasFormula is the more powerfull and dinamic for me.

i am working in the button between blue colors, macro name "Sub LastRowLoopAREAS_IF3()".
I did not clean the macro because I'm studying the codes.

thanks.

[Kenneth Hobs]

Of course you can use error control to handle errors like that for SpecialCells.

I normally build the string and match to the manually built string for Evaluate() and some WorkSheetFunction()'s. I use Debug.Print to print the string so I can compare it. I like to use q="""" for the encapsulated quote characters rather than the traditional double quotes trick.

Sub sIsText()  
  MsgBox Evaluate("=SUMPRODUCT(--ISTEXT(A:A))")
End Sub


Sub Test_CountColIfText()
  MsgBox CountColIfText([A1])
End Sub


Function CountColIfText(aRange As Range) As Long
  CountColIfText = Evaluate("=SUMPRODUCT(--ISTEXT(" & _
    Columns(aRange.Column).Address & "))")
End Function

[xman2000]

Perfect ! Kenneth Hobs!

thank you very mutch !

great programmers in this forum!
i want participate more of this forum.
this is a part of a big dinamic array i making to map the wanted values automaticaly.

solved and closed.
thanks !