200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello All,
I have data in column A which gets updated every month and have formula in column AE based on column A. Problem is, each time new data is added in column A, I have to manually extend the column AE till last row of column A. Could you please send a VBA code that will extend the formula in AE till last row of A.
Just make the formula fill the entire column AE and wrap it in an IF statement that checks if there is anything in the same row in column A and, if nothing is there, just show a blank instead of the formula.
You could do something like this:
=IF(NOT(ISBLANK(A1)),"Your output.","")I don't have a copy of your formula nor your version of Excel but by 'extend the column AE till last row of column A' I imagine that you are extending a 'range' in your formula to accomodate the furthest row. I suggest that you use a dynamic named range (using the OFFSET function) and substituting the named range in your formula. A simpler solution would be to convert your data into a Table (Excel 2007 and later) and your formula in AE will be automatically updated as you enter data in column A.
Hello DanB22000, Thank you for your reply. However I tried this method, but the problem is my data is coming from Webquery which has connection. So when you create a table on the data, it will remove the connections which I cannot remove.