VBA Excel: fill formula till the last used range
Hello experts,
I would like to add the following procedure to my personal.xlsb
Context:
When I add a formula in a specific range: In my example attached C1. I would like, instead of dragging manually the formula, to have a procedure that cover the following requirement:
1-Display inputbox that says "Select the cell in which is your formula
2-Fill formula till the last used range. Last used row number should take into account the last used range of the sheet.
I attached an example with my initial data: Input sheet and with the final result Final-Result sheet after I run the procedure.
If you have questions, please contact me.
I would like to add the following procedure to my personal.xlsb
Context:
When I add a formula in a specific range: In my example attached C1. I would like, instead of dragging manually the formula, to have a procedure that cover the following requirement:
1-Display inputbox that says "Select the cell in which is your formula
2-Fill formula till the last used range. Last used row number should take into account the last used range of the sheet.
I attached an example with my initial data: Input sheet and with the final result Final-Result sheet after I run the procedure.
If you have questions, please contact me.
No file attached I'm afraid.:)
ASKER
Here it is.
Fill-formulas-till-last-used-range..xlsx
Fill-formulas-till-last-used-range..xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tested and it works. Thank you again for your help!!!
You'r welcome! Glad it worked as desired.
Subodh has provided you with what you asked for but are you aware of this trick for filling formulas down a column using the mouse without having to drag or select?
With the cell in which you have entered the formula hover the mouse cursor over the bottom right corner of the cell and it will change to a + symbol. Double click at that point and it will fill down the cell contents until it reaches the last occupied cell of a neighbouring column. So in your example, you have entered the formula in C1 and you have contiguous data down to row 21. Double clicking the bottom right of C1 will fill column C down to row 21.
With the cell in which you have entered the formula hover the mouse cursor over the bottom right corner of the cell and it will change to a + symbol. Double click at that point and it will fill down the cell contents until it reaches the last occupied cell of a neighbouring column. So in your example, you have entered the formula in C1 and you have contiguous data down to row 21. Double clicking the bottom right of C1 will fill column C down to row 21.