[Solved] Define range of formula on VBA based on first blank cell

[kurtwagner]

Hello everybody,

I'm not an expert in VBA, and I need help for the following problem:

I have a column with a lot of values that must be summed. The number of filled rows for this column will vary.

There is a blank row on the top of the column. So far I have this:

Please Login or Register  to view this content.

The only problem is that I don't want a range written like R[-530]:R[-1]C, because the number of rows will vary. I want instead for the macro to find the first blank space before the selected cell and use the first cell under it as the start of the range. The end of the range will be the cell above the formula.

Any ideas?

[YvorL]

Hello!

Try this:

Please Login or Register  to view this content.

Then type =MyReallyCoolSumFunction() to the cell.
(it not so cool, but it works

[kurtwagner]

It works! But is there a way to put it as a sub, not as a function, so that people can see the conventional formula? (=sum('Selected Range')) ?

Thanks

[YvorL]

Of course, but it'll cost you a ticket to the olympics

Please Login or Register  to view this content.

It's a little different, because you have to start the search 1 cell above. In this case when the search is running the active cell is empty. Be careful when you run the macro, the active cell can't be in the first row, or you get an error.

[kurtwagner]

It works incredibly well!

Thank you a lot! Problem solved!

[YvorL]

Happy to help.
Please mark the post as solved.
Give rep if you think so