R/T error 1004 - But WHY?

[JMay]

ActiveCell.Formula = "=Counta(Selection.Offset(-1).end(xlUp) & "":"" &
Selection.Offset(-1))" 'Top to bottom

 

[Sam Wilson]

Replace:

"=Counta(Selection.Offset(-1).end(xlUp) & "":"" & Selection.Offset(-1))"

with

"=Counta(" & Selection.Offset(-1).end(xlUp).address & ":" &
Selection.Offset(-1).address & ")"

Sam

 

[Jim Thomlinson]

That formula is not valid... Everything is inside the quotes so your formula
is exactly what is typed. Additionally the default return value of a range is
the value and not the address. You want something more like...

ActiveCell.Formula = "=Counta(" & Selection.Offset(-1).end(xlUp).address &
":" &
Selection.Offset(-1).address & ")" 'Top to bottom

 

[JMay]

Jim,
Thank you so much - 1) for your fine/understandable explanation and 2)
the revised formula - WORKS GREAT!!
Jim

 

[Dave Peterson]

This may not fit, but if you're trying to count the number of cells filled
between row 1 and the row above, you could use:

activecell.formular1c1 = "=counta(r1c:r[-1]c)")

r1c is row 1 of the same column
r[-1]c is the row above of the same column

If you had headers in rows 1:2 that you wanted to avoid:
activecell.formular1c1 = "=counta(r3c:r[-1]c)")