Selecting a range of definite size (e.g. A1:A2) works just fine, but selecting a range to the last row does not. If relevant, I am using Office 365.
Asked
Active
Viewed 1,210 times
1
-
1Excel does not accept open ended range references like `A1:A` . One must state the ending row or full column reference. – Scott Craner Dec 14 '20 at 19:22
-
@ScottCraner it accepts that in array formulas – phuclv Dec 15 '20 at 00:40
2 Answers
2
You can do open references like A:A or 1:1 but cannot mix and match the fixed and open references. To reference the last non-blank cell:
=INDEX(A:A,COUNTA(A:A))
Or the whole non-blank range:
=A1:INDEX(A:A,COUNTA(A:A))
ExcelEverything
- 3,114
- 1
- 4
- 16
