Count Last Row with data in Range

[ionelz]

Hi,
I have this Range C2:Y1000 and there are empty cells
I need to Count from C2 down which is the last row with data in any cells
I can not use counta(c2:c1000) because may may get 10 but for example caunta(n2:n1000)=25
I need the MAX so column with MAX of counta, that will tell the ROWS with data
Thanks all

 

[johnnyL]

How about:

       LastRowInRange = Range("C2:Y1000").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row             ' Returns a Row Number

 

[ionelz]

could you please write a formula for this ? not vba

 

[johnnyL]

How about:

=SUMPRODUCT(MAX((C2:Y1000<>"")*ROW(C2:Y1000)))

 

[ionelz]

How about:

=SUMPRODUCT(MAX((C2:Y1000<>"")*ROW(C2:Y1000)))

Yes, thank you, for some reason I had to "add" -1 at end of formula but this is what I was looking for...

 

[ionelz]

How about:

       LastRowInRange = Range("C2:Y1000").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row             ' Returns a Row Number

How can I say that A1= value from vba above ? (I want to put the result in A1)

 

[Joe4]

How can I say that A1= value from vba above ? (I want to put the result in A1)

      LastRowInRange = Range("C2:Y1000").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row             ' Returns a Row Number
      Range("A1").Value = LastRowInRange

or more simply:

      Range("A1").Value = Range("C2:Y1000").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row