Hi Group

I'm not too sure that my subject is clear, but this is what I want to do.

In a row I have a range of cells, like this

SSS SSS SSS SSS "empty", SSS SSS PPP WWW TTTT "empty" "empty" YY ZZ

Now I want to make a UDF to count the number of the last continous not empty cells in this row. In this case the answer is 2, as there is an empty cell before the last two not empty.

Regards
Jan

hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

Thank you. Didn't think it could be done with a formula.

regards
Jan

Den onsdag den 14. januar 2015 kl. 03.53.49 UTC+1 skrev isabelle:
hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

Sorry but I think I was a Little too fast there.

No matter how many there are, it always comes to the same result, 1. If I leave out the COUNTA from the formula, and only use

=INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",COLUMN(1 :1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLUMN(1:1) ),1)))

I can see that it returns the content of the last cell which has content. And then it apparently only Counts that.

Jan

Den onsdag den 14. januar 2015 kl. 03.53.49 UTC+1 skrev isabelle:
hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

hi Jan,

you have to validate the formula with ctrl + shift + enter each time you edit it

isabelle

Le 2015-01-14 09:08, a écrit :
Sorry but I think I was a Little too fast there.

No matter how many there are, it always comes to the same result, 1. If I leave out the COUNTA from the formula, and only use

=INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",COLUMN(1 :1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLUMN(1:1) ),1)))

I can see that it returns the content of the last cell which has content. And then it apparently only Counts that.

Jan

Den onsdag den 14. januar 2015 kl. 03.53.49 UTC+1 skrev isabelle:
hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

Jan, if you properly validate the formula, you'll see it appear braces { } on
each side of the formula

isabelle

Yes I know, and of course I forgot, but unfortunately that made it worse. Now it Counts 0. But I figured it out right now I think. My row starts in column B, not A, and when I move it to A it Counts right.
So thank you once Again.

Jan

Den onsdag den 14. januar 2015 kl. 15.31.42 UTC+1 skrev isabelle:
Jan, if you properly validate the formula, you'll see it appear braces { } on
each side of the formula

isabelle

hi Jan,

another solution with VBA
rng can be a whole line or a line part

Function MyCount(rng As Range)
Dim i As Integer
Dim lastCol As Integer
lastCol = Cells(rng.Row, rng.Columns.Count).End(xlToLeft).Column
i = lastCol
While Not IsEmpty(Cells(rng.Row, i))
i = i - 1
Wend
MyCount = Application.CountA(Range(Cells(rng.Row, i), Cells(rng.Row, lastCol)))
End Function

isabelle

correction just in case there is no empty cell

Function MyCount(rng As Range)
Dim i As Integer
Dim lastCol As Integer
lastCol = Cells(rng.Row, rng.Columns.Count).End(xlToLeft).Column
i = lastCol
While Not IsEmpty(Cells(rng.Row, i))
i = i - 1
If i = 0 Then MyCount = 0: Exit Function
Wend
MyCount = Application.CountA(Range(Cells(rng.Row, i), Cells(rng.Row, lastCol)))
End Function

isabelle

Hi Isabelle,

Am Wed, 14 Jan 2015 12:19:05 -0500 schrieb isabelle:

lastCol = Cells(rng.Row, rng.Columns.Count).End(xlToLeft).Column
^^^^^^
you get a wrong result. Change the line above to:
lastCol = Cells(rng.Row, Columns.Count).End(xlToLeft).Column

or try:

Function myCnt(myRng As Range) As Long
Dim varTmp As Variant
Dim i As Long, LCol As Long

LCol = Cells(myRng.Row, Columns.Count).End(xlToLeft).Column
varTmp = myRng
For i = LCol To 1 Step -1
If Len(varTmp(1, i)) = 0 Then
myCnt = LCol - i
Exit Function
ElseIf i = 1 Then
myCnt = myRng.Cells.Count
End If
Next
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Wed, 14 Jan 2015 18:33:40 +0100 schrieb Claus Busch:

Function myCnt(myRng As Range) As Long

if there could be values right of your range you better try:

Function myCnt(myRng As Range) As Long
'Only for ranges A:Z
Dim varTmp As Variant, varRng As Variant
Dim i As Long, LCol As Long

varRng = Split(myRng.Address(0, 0), ":")
LCol = Asc(Left(varRng(1), 1)) - 64

varTmp = myRng
For i = LCol To 1 Step -1
If Len(varTmp(1, i)) = 0 Then
myCnt = LCol - i
Exit Function
ElseIf i = 1 Then
myCnt = myRng.Cells.Count
End If
Next
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Optionally...

Function GetLastGroupedCols(Rng As Range)
Dim Col1&, Col2&
Col1 = Cells(Rng.Row, Columns.Count).End(xlToLeft).Column
Application.Volatile
If Cells(Rng.Row, Col1).Offset(0, -1) = "" Then
GetLastGroupedCols = 1
Else
Col2 = Cells(Rng.Row, Col1).End(xlToLeft).Column
GetLastGroupedCols = Col1 - Col2 + 1
End If
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion