Adding Rows - Macro

GallowayJay · Apr 9th, 2002, 08:57 AM

Hello!

I want to be able to automatically add rows to a particular sheet. My ultimate goal is to have an individual enter the number of rows they desire. Then the sheet would provide the amount of row space requested. I intend to protect the sheet from unauthorized adjustments, but in order to satisfy the row requirement, I need some macro (or function) in place. The Alt-Insert-Rows will not work in a protected sheet. Therefore, I need an alternative. Any ideas from the gurus of this board?

Al Chara · Apr 9th, 2002, 09:05 AM

To add rows to a protected worksheet you have to first unprotect the worksheet, like the following:

ActiveSheet.Unprotect
[a1].EntireRow.Insert
ActiveSheet.Protect

If you want the user to specify the number of row to add, try the following:

Dim NumOfRows As Long
NumOfRows = InputBox("Enter Number of Rows to Add")
ActiveSheet.Unprotect
For i = 1 To NumOfRows
[a1].EntireRow.Insert
Next
ActiveSheet.Protect
_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-09 08:07 ]

poleary2000 · Apr 9th, 2002, 09:06 AM

Al Chara,

How does that work is the worksheet is protected with a password?

Al Chara · Apr 9th, 2002, 09:11 AM

ActiveSheet.Protect "YourPassword"
ActiveSheet.UnProtect "YourPassword"

GallowayJay · Apr 9th, 2002, 11:02 AM

Al,

Thanks alot for the help! It is working, but for one problem - I am looping continuously until the file crashes. I cannot determine exactly what the next step is at this point.

GallowayJay

Al Chara · Apr 9th, 2002, 01:32 PM

It shouldn't loop forever, it should just loop for the number of rows to add. What code are you using?

Apr 9th, 2002, 09:06 AM	#3
poleary2000 Board Regular Join Date: Apr 2002 Location: Pittsburgh, PA Posts: 317	Al Chara, How does that work is the worksheet is protected with a password? __________________ Thanks in advance, Patrick

Apr 9th, 2002, 09:11 AM	#4
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	ActiveSheet.Protect "YourPassword" ActiveSheet.UnProtect "YourPassword" __________________ Kind regards, Al Chara

Apr 9th, 2002, 01:32 PM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	It shouldn't loop forever, it should just loop for the number of rows to add. What code are you using? __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 08:57 AM	#1
GallowayJay New Member Join Date: Apr 2002 Posts: 15	Hello! I want to be able to automatically add rows to a particular sheet. My ultimate goal is to have an individual enter the number of rows they desire. Then the sheet would provide the amount of row space requested. I intend to protect the sheet from unauthorized adjustments, but in order to satisfy the row requirement, I need some macro (or function) in place. The Alt-Insert-Rows will not work in a protected sheet. Therefore, I need an alternative. Any ideas from the gurus of this board?

Apr 9th, 2002, 09:05 AM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	To add rows to a protected worksheet you have to first unprotect the worksheet, like the following: ActiveSheet.Unprotect [a1].EntireRow.Insert ActiveSheet.Protect If you want the user to specify the number of row to add, try the following: Dim NumOfRows As Long NumOfRows = InputBox("Enter Number of Rows to Add") ActiveSheet.Unprotect For i = 1 To NumOfRows [a1].EntireRow.Insert Next ActiveSheet.Protect _________________ Hope this helps. Kind regards, Al. [ This Message was edited by: Al Chara on 2002-04-09 08:07 ]

Apr 9th, 2002, 11:02 AM	#5
GallowayJay New Member Join Date: Apr 2002 Posts: 15	Al, Thanks alot for the help! It is working, but for one problem - I am looping continuously until the file crashes. I cannot determine exactly what the next step is at this point. GallowayJay