Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Adding Rows - Macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?



  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al Chara,

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

    Thanks in advance,
    Patrick

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveSheet.Protect "YourPassword"
    ActiveSheet.UnProtect "YourPassword"
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It shouldn't loop forever, it should just loop for the number of rows to add. What code are you using?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •