Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
HI , and a good day to all programmers
I need a UDF , which to count non blank cells above the cell which contain this UDF . When the UDF find the first non blank cell , to stop the count . Example : A1  nonblank A2  blank A3  blank A4  blank A5  UDF here , the result of UDF from cell A5 must be 3 . I need to count only above in the column , not in the left or right . Thank you 
#2
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
I need a UDF , which to count non blank cells
* sorry , to count blank cells . 
#3
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
Hi,
Blank is an often confusing term with regard to Excel and here I've taken you literally i.e. nothing on the cell, totally empty. Call with =countblanks() Function countblanks() For x = ActiveCell.Row  1 To 1 Step 1 If Cells(x, ActiveCell.Column).Formula = vbNullString Then countblanks = countblanks + 1 Else Exit Function End If Next End Function  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ytayta555" wrote: HI , and a good day to all programmers I need a UDF , which to count non blank cells above the cell which contain this UDF . When the UDF find the first non blank cell , to stop the count . Example : A1  nonblank A2  blank A3  blank A4  blank A5  UDF here , the result of UDF from cell A5 must be 3 . I need to count only above in the column , not in the left or right . Thank you . 
#4
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
After the first line, add:
Dim x as Long Application.Volatile The first line because you might get a compile error if you use Option Explicit, the second because the function will otherwise not recalculate if you fill a cell above the calling cell afterwards.  Kind regards, Niek Otten Microsoft MVP  Excel "Mike H" wrote in message ... Hi, Blank is an often confusing term with regard to Excel and here I've taken you literally i.e. nothing on the cell, totally empty. Call with =countblanks() Function countblanks() For x = ActiveCell.Row  1 To 1 Step 1 If Cells(x, ActiveCell.Column).Formula = vbNullString Then countblanks = countblanks + 1 Else Exit Function End If Next End Function  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ytayta555" wrote: HI , and a good day to all programmers I need a UDF , which to count non blank cells above the cell which contain this UDF . When the UDF find the first non blank cell , to stop the count . Example : A1  nonblank A2  blank A3  blank A4  blank A5  UDF here , the result of UDF from cell A5 must be 3 . I need to count only above in the column , not in the left or right . Thank you . 
#6
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
On 22 feb., 16:19, "Charabeuh" wrote:
hello, When using the function, it works the first time. INDEED . I was busy to check it I added Application.volatile in the code of Mike H ..... I use this code : Sub MYMACRO() Dim myCell As Range Dim myRng1 As Range Set myRng1 = Range("A1:E30") For Each myCell In myRng1.Cells If myCell < vbNullString Then myCell = "=countnull()" End If Next myCell End Sub and now , IT WORKS great . I have to thank you  to all three  very much , that you used and shared your knowledge and time helping me . I wish you a good week 
#7
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
Hi everybody I had posted this a while ago , and I get a good function : Function countnull() Dim X Application.Volatile If TypeName(Application.Caller) = "Range" Then For X = Application.Caller.Row  1 To 1 Step 1 If Cells(X, Application.Caller.Column).Formula = vbNullString Then countnull = countnull + 1 Else Exit Function End If Next X End If End Function this function brings me the value of cells empty from above , in the same Column ; Now , I have need this function to do the same task , but counting empty cells in the same Row ; I need to count to the left cells where this function is . 2) .Second ask , I have need to have a function doing the same task (meaning counting empty cells ) but to return me the value of empty cells after it find a specific value , such as  " 3 " . 3) . Well , if it is possible , I really have need and a function to do the same task like for I asked just above , but to not do differences beetween empty or non empty cells , just bring me the value of number of cells passing until it find value  3  . thank you . 
#8
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
There is no problem if it is not in a code , is not a UDF ,
it is great and if it is done in formulas , too . 
#9
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
Hi,
Am Mon, 30 Nov 2020 04:47:57 0800 (PST) schrieb Xxer Xxes: this function brings me the value of cells empty from above , in the same Column ; Now , I have need this function to do the same task , but counting empty cells in the same Row ; I need to count to the left cells where this function is . 2) .Second ask , I have need to have a function doing the same task (meaning counting empty cells ) but to return me the value of empty cells after it find a specific value , such as  " 3 " . 3) . Well , if it is possible , I really have need and a function to do the same task like for I asked just above , but to not do differences beetween empty or non empty cells , just bring me the value of number of cells passing until it find value  3  . I don't know if I understood your problem correctly. Have a look: https://1drv.ms/x/s!AqMiGBK2qniTge9d...95CDA?e=DHuVMz Regards Claus B.  Windows10 Office 2016 
#10
Posted to microsoft.public.excel.programming




Need a UDF to count blank cells
I tried to work with that functions , but I realize i need
3 UDF ; 1) Function countnull() Dim X Application.Volatile If TypeName(Application.Caller) = "Range" Then For X = Application.Caller.Row  1 To 1 Step 1 If Cells(X, Application.Caller.Column).Formula = vbNullString Then countnull = countnull + 1 Else Exit Function End If Next X End If End Function what is doing this udf in a column , to do it in a row , counting in the same row , on left side ; 2) second udf : instead to count empty cells ( untill udf find a cell non empty) and return number of count empty cells , to do tha same loop and count untill find value 3 ( number three )      i.ll try to make some exemples ; for 1 ask): now , this formula return so : in Column C , cell C1 for eg i have value 1 ; C2 ,C3 ,C4 , C5 are empty :introduceing in cell C5 my actually udf , returns value 4 , and is correct ; it ehows me there are 4 cells "consecutivelly " empty , before to find the cell C1 , which is not empty ; well ,now i need to do tha same task , in a Row , not in a column , as i said , "to the left " . so , the results of this new udf will be : assume we have in Row10 , column B , we have value 2 ( the mean is cell is not empty ); c10 and d10 is empty ; in the same row ( we work with only one row in this udf ) , Column E ,we put this new formula ; the result must to be 2 ; if in cell d10 is any value , in e10 the result displaied will be 0 ( zero ) .       For second demand ) : udf to count untill find a value , 3 for eg. ignoring wether cells are empty or not 
#11




Try this:
Blank is an often confusing term with regard to Excel and here I've taken
you literally i.e. nothing on the cell, totally empty. Call with =countblanks() Function countblanks() For x = ActiveCell.Row  1 To 1 Step 1 If Cells(x, ActiveCell.Column).Formula = vbNullString Then countblanks = countblanks + 1 Else Exit Function End If Next End Function Regards, Jerry 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how to count#cells w/= value in other column and not count blank c  Excel Worksheet Functions  
count blank cells to next value  Excel Discussion (Misc queries)  
Count from Blank & NonBlank Cells  Excel Discussion (Misc queries)  
Count the non blank cells  Excel Worksheet Functions  
Count the non blank cells  Excel Worksheet Functions 