Without VBA, I am trying to refer a range that starts at A2 and never ends. For example, if I want row 2 till row 5 i’d do

```
$A$2:$A$5
```

But what if I want the end to be open?

```
$A$2:??
```

Is this possible?

Depending on what’s in A1 and what formula you’re putting the reference into, you could simply use A:A. For example, if you wanted to sum all of the values in column A, but A1 contained a column title rather than a number, you could still write =SUM(A:A) and the title in A1 would just be ignored.

### Answer：

A2:A works in many formulas

hope that helps

### Answer：

If you want to refer to a range starting from A2 until max row (1048576 or 65536 for Excel prior to 2007), you can use this volatile formula… `=OFFSET(A2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1)`

. Use formula as a defined range name or inside other formula which takes range as an argument (for eq SUM)…

### Answer：

This well covered in VBA as code below:

```
Range("A2", Range("A2").End(xlDown))
```

And if you want reach that in formula, it depends on the version number of your MS-Excel.

According to this reference number of all rows are in a sheet from Excel 2007 onwards are 1048576 that you can use bellow:

```
$A$2:$A$1048576
```

Because this range in formula is depended on Excels version, this may be different in future versions.

Finally, I suggest you use VBA.

Tags: excelexcel, vba