VBA code to copy formula till end

0

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.    

Answer
Discuss

Discussion

READ THE FORUM RULES (or even just the instructions on this page) that tell you how to reply to or discuss an answer)!
don (rep: 1297) Feb 16, '17 at 8:13 pm
Add to Discussion

Answers

0

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.","")
Discuss
0

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.  

Discuss
0

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. 

Discuss

Discussion

So I am using this VBA code to extend till last row for multiple columns which is taking the reference of column A:

Sub CheckBox1_Click() Dim LastRow As Long       LastRow = Range("A" & Rows.Count).End(xlUp).Row          Range("AH123:AV123").Copy Range("AH123:AV" & LastRow)      End Sub

This code is working absolutely fine, but problem is, I have to run this code manually by Alt + F8 and then run. so if there is any way that it gets run automatically whenever i have additional data in column A, would be a great help.
grovergeetesh Feb 16, '17 at 12:43 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login