Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Regular
    Joined
    Feb 2016
    Posts
    74
    Location

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

    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"


    HTML Code:
    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


  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Feb 2016
    Posts
    74
    Location
    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.
    Last edited by xman2000; 11-04-2016 at 04:14 PM.

  4. #4
    VBAX Regular
    Joined
    Feb 2016
    Posts
    74
    Location
    "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.

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,911
    Location
    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
    Last edited by Kenneth Hobs; 11-04-2016 at 05:18 PM.

  6. #6
    VBAX Regular
    Joined
    Feb 2016
    Posts
    74
    Location
    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 !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •