View Full Version : [SOLVED] Count Rows EntireColum one Shot in VBA CountA IsNumber, IsEmpty, Istext, HasFormulas

xman2000

11-04-2016, 01:10 PM

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

17514

17513

Kenneth Hobs

11-04-2016, 02:00 PM

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

11-04-2016, 04:03 PM

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

11-04-2016, 04:45 PM

"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

11-04-2016, 05:05 PM

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

11-04-2016, 08:07 PM

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 !

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.