Need a UDF to count blank cells
in [Programming]
|
Prev: Timer that auto-closes workbook?
Next: seperate
From: ytayta555 on 22 Feb 2010 06:54 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
From: ytayta555 on 22 Feb 2010 06:56 > I need a UDF , which to count non blank cells * sorry , to count blank cells .
From: Mike H on 22 Feb 2010 07:50 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 > . >
From: Niek Otten on 22 Feb 2010 09:02 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" <MikeH(a)discussions.microsoft.com> wrote in message news:BCE11A57-DFD1-42B4-A6CC-4F894B01159E(a)microsoft.com... > 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 >> . >>
From: Charabeuh on 22 Feb 2010 09:19
hello, When using the function, it works the first time. example: A1:A10 are "Null" into A11 put the formula =countblanks() ==>the result in A11 is 10 Now insert the value 5 in A5 ==> the result is still 10. I added Application.volatile in the code of Mike H as you have suggested. With the same example as above the result becomes 4 (the number of blank cell above A5) it is perhaps the use of 'ActiveCell' that made this behaviour to occur. I have changed the code of Mike and replace 'ActiveCell' with 'Application.Caller'. It seems to improve the behaviour of the 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 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' "Niek Otten" <nicolaus(a)xs4all.nl> a écrit dans le message de groupe de discussion : 9A12214B-24D6-4BA6-9230-D6B6CBC02AD5(a)microsoft.com... > 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" <MikeH(a)discussions.microsoft.com> wrote in message > news:BCE11A57-DFD1-42B4-A6CC-4F894B01159E(a)microsoft.com... >> 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 >>> . >>> > |