MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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