Need Macro for Adding Rows
Posted by Shaun Culp on October 22, 2001 7:55 PM
I have a spreadsheet in which the first 3 Rows are locked and frozen (titles and summary data), the next 3 rows are unlocked and are for data entry, and the last 3 rows are again locked and have various formulas. The 3 rows in the middle (rows 4,5,and 6)need to be able to expand to accomodate more data. I want a macro that every time adds a new row before the last of the unlocked rows. That is, a macro that copies row 6, then inserts that copied row between row 5 and 6. However, I need the macro written so that the next time it is run, it copies the last unlocked row (now row 7), and inserts above it (between 6 and 7). I just can't figure out how to make it do this! Thanks for any help you can provide!
Posted by craigD on October 22, 2001 9:29 PM
Try this - a bit rough but will do the job
Sub Macroinsertrow()
'This code finds where to insert the row by checking the locked or unlocked status
'of cells in column A. It assumes the 3 rows at the bottom are locked
Dim firstunlockedcell As String
Range("A1").Select
Do Until ActiveCell.Locked = False
ActiveCell.Offset(1).Select
Loop
lastunlockedcell = ActiveCell.Address
Do Until ActiveCell.Locked = True
ActiveCell.Offset(1).Select
Loop
Locked = ActiveCell.Address
ActiveCell.Offset(-1).Select
Selection.EntireRow.Insert
End Sub
Posted by Geoffrey Hurst on October 23, 2001 2:04 AM
Assuming that there is always data in column A of the last row on the worksheet and that the sheet is not protected with a password :-
Dim LR As Range
Set LR = [A65536].End(xlUp).Offset(-3, 0).EntireRow
ActiveSheet.Unprotect
LR.Copy
LR.Offset(1, 0).Insert
ActiveSheet.Protect
Posted by Geoffrey Hurst on October 23, 2001 2:14 AM
Or ...
Or :-
Dim LR As Range
Set LR = [A65536].End(xlUp).Offset(-3, 0).EntireRow
ActiveSheet.Unprotect
LR.Copy
LR.Insert
ActiveSheet.Protect
