VBA to automatically extend a SUM formula

Hi

I have a relatively basic table and am trying to automatically populate/refresh the total in a column after someone inserts a new row at the end of the column, but the code I have found doesn't seem to work (in spite of comments in the earlier threads suggesting it does work - am I misinterpreting something, or did they tweak something..?)

I have data in column Q, ranging from Q5 to Q44 (rows 1-4 are heading rows).
I have a simple SUM formula in Q45 - i.e. SUM(Q5:Q44)

Nice and easy so far...

The user can insert a row beneath the last line of data, the new row would be row 45, with the SUM moving down to row 46. As expected, the SUM refers to the original range (Q5:Q44) but needs updating to (Q5:Q45). Unfortunately, the user cannot be trusted to amend the SUM range or to copy it across to the other columns. I am trying to create a macro which does this automatically.

The code I have found is:

Please Login or Register  to view this content.

(the variables are populated elsewhere in the macro, rather than being entered manually within the macro - the issue is not with the variables, but with the SUM formula)

However, when I run this, I get a #NAME? error and the formula in the cell is =SUM('Q5':'Q45') (with the single quotes) - i.e. the range has been updated correctly, but the single quotes are causing a problem.
I can create a simple macro to remove the quote marks (using the edit/replace (Ctrl+H) function - I'm a VBA novice, so don't know a better way), but I assume there would be a minor tweak to get the macro to populate the formula correctly, i.e. =SUM(Q5:Q45)


P.S. I have considered, but discarded, using a named range to automatically extend the range, but am not sure that would work because the SUM row is directly beneath the data and it would be tricky (not impossible) to identify a blank cell, for example, to indicate the end of the dynamic named range


Can someone help, please?

Thanks,
Graham

Brilliant MarMo - that works perfectly!!! Thank you very much and thank you for the prompt reply!!

(and thanks for the brief explanation too!)