Macro for Adding Rows


Posted by Shaun Culp on October 25, 2001 10:08 AM

A couple of days ago a Geoffrey Hurst replied to my request for a macro. The one he gave me works just as I needed it to, but now I need it to do one more thing. My protected spreadsheet has three locked and formula-laden rows, followed by some unlocked data-entry rows, and followed again by three more locked rows. The macro always copies the last *unlocked* row, and inserts a new row just above it. In this way, each time a new row is needed for data entry, the macro always puts it second from the bottom of the unlocked data entry rows. What I need now is to know how to add to this macro so that when the NEW row is added (2nd to the last of the unlocked rows), it runs a "clear contents" on the new row, and sets the active cell under the first -A- column of this row. This way, the user will be right where they need to be to enter the new data in this new row. Here is the macro I was given before:

Dim LR As Range
Set LR = [A65536].End(xlUp).Offset(-3, 0).EntireRow
ActiveSheet.Unprotect
LR.Copy
LR.Insert
ActiveSheet.Protect

***Thanks in advance for any help you can give!




Posted by Jim Baxter on October 25, 2001 3:24 PM


Dim LR As Range
Set LR = [A65536].End(xlUp).Offset(-3, 0).EntireRow
ActiveSheet.Unprotect
With LR
.Copy
.Insert
.Offset(-1, 0).ClearContents
.Cells(1, 1).Offset(-1, 0).Select
End With
ActiveSheet.Protect