# Formula in VB where number of Columns are unknown

#### Carly

##### Active Member
We are trying to use a CountA formula on a spreadsheet where the amount of Columns change each time the report is run. We have had this before where we wanted the formula to be at the end of each column and used the following formula:

With [A65536].End(xlUp).Offset(1).Resize(, [IV1].End(xlToLeft).Column)
.Formula = "SUM(R2C:R[-1]C)!
.Value = .Value
End With

This then created a Sum formula against each column.

but this time we would like the formula to be at the end of each row (no. of rows will change also), so we revamped the formula to the following:

With [IV1].End(xlToLeft).Offset(1, 1).Resize(, [A65536].End(xlUp).Column)
.Formula = "=COUNTA(RC6:RC[-1])"
.Value = .Value
End With

This puts the formula in the cell where we want it to start but it doesn't go all the way down.

Is there anyway that this can be done?

Carly

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
This:

[A65536].End(xlUp).Column

should always return 1, because the column of A is always 1. So, try changing the .Column to .Row on that statement.

We tried this and it gave the following error:

Run Time Error 1004:

Application-defined or object-defined error

The Resize method works exactly as selecting using the mouse or the keyboard in Excel.

If you resize a range in Excel you should see something like

10Rx5C

that means 10 rows by 5 columns.

The .Resize method expects the same

.Resize(NumberOfRows, NumberofColumns)

and, you're giving, in your code, the current number of rows

<pre>.Resize(,</pre>

with the "found" number of columns in [A65536].End(xlUp)

<pre> [A65536].End(xlUp).Column) </pre>
So, if you "switch" the order, to the "right" order, that is, give the found rows in column A, and the current number of columns (Which I think is 1 in this case), you should have

<pre>.Resize([A65536].End(xlUp).Row,1)</pre>

Cheers that's worked fine!!!!!

Replies
0
Views
295
Replies
3
Views
121
Replies
1
Views
108
Replies
1
Views
261
Replies
3
Views
219

### Forum statistics

1,202,983
Messages
6,052,908
Members
444,611
Latest member
ggwpnore ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back