Excel VBA auto-fill and auto-update

[BungleNZ]

Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm relatively new to VBA and am having problems with some coding. I have a worksheet with many columns of data, and 3 of the columns of data (K, L, M) have formulae that I want to auto-fill down when a user inserts a new row. The formula cells start at row 7, and currently end at cell 145, but this obviously changes when a user deletes or inserts a row. All rows outside this row range (7:145) are going to be locked in a protected sheet, so the inserting and deleting of rows will only occur within this range. There is no problem in the vba code auto-filling down to the end of each column if that's easiest, as the formula in each cell returns "" to the cell if no information in earlier cells is present.<o></o>
<o></o>
Assuming this is easy enough, I am trying to have it so that the coding is automatically run when a new row is inserted - as opposed to having to manually run the macro.<o></o>
<o></o>
It's probably no help, but the code I have produced at the moment is (I replicate this code for the other columns as well, which I know is not efficient code):<o></o>
<o></o>
Sub Fill()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("k7").AutoFill Destination:=Range("k7" & LR)
End Sub<o></o>
<o></o>
<o></o>
Any help that anyone can provide is very much appreciated.<o></o>
<o></o>
Thanks<o></o>
<o></o>
Bungle<o></o>

 

[nigelk]

This should work. I think you are just missing the extra K

Sub Fill()

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("k7").AutoFill Destination:=Range("k7:k" & LR)

End Sub

 

[Peter_SSs]

Bungle

See if this works for you.

1. In a vacant column (I have used column Q), put a formula like this in row 1.
=ROW(Q145)
where Q is the column the formula is in, and 145 is whatever your current last unlocked/unprotected row is.

2. If you wish, hide this column.

3. Copy this code into the particular worksheet module, not a standard module. Adjust the "Q" values to whatever column you chose to put the formula in at step 1.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    lr = Range("Q1").Value<br>    <SPAN style="color:#00007F">If</SPAN> lr > Range("Q2").Value <SPAN style="color:#00007F">Then</SPAN><br>        Range("K7:M" & lr).FillDown<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Range("Q2").Value = lr<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[BungleNZ]

Bungle

See if this works for you.

1. In a vacant column (I have used column Q), put a formula like this in row 1.
=ROW(Q145)
where Q is the column the formula is in, and 145 is whatever your current last unlocked/unprotected row is.

2. If you wish, hide this column.

3. Copy this code into the particular worksheet module, not a standard module. Adjust the "Q" values to whatever column you chose to put the formula in at step 1.

Private Sub Worksheet_Calculate()
Dim lr As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
lr = Range("Q1").Value
If lr > Range("Q2").Value Then
Range("K7:M" & lr).FillDown
End If
Range("Q2").Value = lr
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Thanks. That all works reasonably well.

A couple of issues though - the macro will not run when I protect the sheet (which I have to do as I am not the user), - but I solved this problem with "ActiveSheet.Unprotect Password:="-"" and "ActiveSheet.Protect Password:="-", DrawingObjects:=True, Contents:=True, Scenarios:=True"

But the problem is, when the worksheet automatically protects itself after inserting a row, it removes the ability to insert any further rows (all the protect check options are automatically unchecked).

Any suggestions on how to resolve this?

The only other thing is that when a row is deleted, it causes #REF errors, until a new row is inserted (I guess I need to have the code working for when a row is deleted as well as inserted?)

Thanks

 

[Peter_SSs]

It appears that you have started another thread on this:
http://www.mrexcel.com/forum/showthread.php?t=342462

If the only remaining problem is the #REF error (I am guessing this is in cell Q1, and this should only occur if the last row of the unlocked range is deleted), then try changing the formula in Q1 to:
=ROW(Q146)-1
where the 146 points to the first row after the unlocked section.

If this is not the problem, tell us more about where the #REF errors are, what formula was in the cells before the row was deleted and which row was deleted.

 

[BungleNZ]

It appears that you have started another thread on this:
http://www.mrexcel.com/forum/showthread.php?t=342462

If the only remaining problem is the #REF error (I am guessing this is in cell Q1, and this should only occur if the last row of the unlocked range is deleted), then try changing the formula in Q1 to:
=ROW(Q146)-1
where the 146 points to the first row after the unlocked section.

If this is not the problem, tell us more about where the #REF errors are, what formula was in the cells before the row was deleted and which row was deleted.

Thanks for your reply.

I really don't think the problem is in the coding, the Q1 reference cell we created, or even the formulae in the cells - it's due to the fact that when a row below is deleted the formula has its referenced cells deleted and #REF! is displayed within the resulting formula. Although it probably wont mean much, and I don't think it matters much, here is the correct formula in the cell being affected:

In row 9 and column K
=IF(AND(J9<>"",SUM(D10:I10)>0),SUM(D10*D9,E9*E10,F9*F10,G9*G10,H9*H10,I9*I10)/J9,"")

Here's the resulting formula when the row below is deleted:

=IF(AND(J9<>"",SUM(#REF!)>0),SUM(#REF!*D9,E9*#REF!,F9*#REF!,G9*#REF!,H9*#REF!,I9*#REF!)/J9,"")

However, this #REF! error is removed when the formula from the cell above is filled down (as I have sufficient constraints in the formula to resolve this.)

Because of this, I think that all I need to do is have some coding that will complete an autofill when the rows are deleted or inserted, as opposed to just the currently coded row insertion.

Does this make any sense?

Thanks again for your help

 

[Peter_SSs]

Try changing:

If lr > Range("Q2").Value Then

to

If lr <> Range("Q2").Value Then

 

[BungleNZ]

Try changing:

If lr > Range("Q2").Value Then

to

If lr <> Range("Q2").Value Then

Brilliant! Works a treat. Thank you very much for persevering with this one.