CODEWITHSUNDEEP
excelcellautofillvba
user3598259
user3598259

Reputation: 3

How to Autofill cells up to a certain row based on the value in a different cell?

So I have a cell (let's say C3) and I have a column in A.

Let's say that the formula I want to fill for the cells in columnA is A2 = A1 + 1 and so forth. I have the number 30 in C3.

I want excel to fill exactly 30 cells down column A using that previous formula (A2 = A1 + 1). If I change the number in C3 to 50, then excel will automatically add 20 more rows in column A following the same pattern.

If I change the number in C3 to 10, excel will automatically delete the rows until the first 10 rows remain.

EDIT TO ADD: OK so I guess I have to use macros. Anyone suggest the VBA code for this?

Upvotes: 0

Views: 15214

Answers (1)

Nicholas Flees
Nicholas Flees

Reputation: 1953

You can avoid VBA if you know a maximum possible number of rows. Use the following formula in A2 and apply it downward until you've applied through the maximum number of columns.

=IF(ROW()<=$C$3,A1+1,"")

So in reality, you still have a formula in these cells, but you won't see any output until they are supposed to be seen. It's a work-around.

Upvotes: 2

Related Questions