Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Using the + sign to add/insert rows in a worksheet (Excel 2003)

    Hi all....I use a number of books as Excel references, but can't seem to find what I need in any of them....I am attaching a sample of a time sheet that I have made. Initially, it was about 40 rows (allowing for 1 or 2 entries per working day in a month). However, when it is printed on a single sheet of paper, it is difficult to see & read. If all of the rows are not used, even hiding unused rows doesn't permit what remains to 'expand' (filling the paper sheet and becoming easier to read). I need about 40 rows, but I am looking for a way to set it up initially as, say, 15 rows and then place a plus sign (on the left hand side) that can be clicked to expand the worksheet as needed by inserting rows as needed....and also have any newly-inserted rows also contain all of the formulae that exist in the rows above, and will be calculated in the totals on the bottom....I can't find anything in these texts that show me how to add this expansion feature to a worksheet...I am attaching a sample as it exists right now (I removed a lot of stuff to get it down to the 100k size).
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    Hi David

    I think this Sub will do what you want. However, I have not got it to run from a button it does add rows to the bottom, above the totals section when run. You could attach it to a button on the menu to work the code is:

    Sub AddRow()
    Dim LastCell As Range
    With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "I").End(xlUp)
    If IsEmpty(LastCell) Then

    Else
    Set LastCell = LastCell.Offset(-2, 0)
    End If
    End With
    LastCell.Select
    Selection.EntireRow.Copy
    Selection.EntireRow.Insert



    End Sub
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    Ooopps Sorry about that, add this just above End Sub

    Application.CutCopyMode = False

    For the other bit about page set up, let me fiddle around a bit and have a look
    Jerry

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    Wow..thank you, Jerry...so far, it works like a charm....2 questions: when it inserts a row, the newly-inserted row is shown in a shimmering border..(which goes away if I hit the ESC key...is there a piece of code that do that for me?

    ...secondly, right now the Page Setup (and Page Preview) shows the sheet on 1 page...I would like to have it be as large as possible on a single sheet (making it easier to read)....is there a way to set it up so that, for example, a 10-row sheet occupies the entire page (and making it 3x easier to see) than a 30-row sheet occupying the same sheet?.....I want to start with, say 10-12 rows, and make the AddRow available so that people can add rows only as they need them....that way, the fewer rows used (or added) the easier it will be to see the sheet & contents (and only becomes more difficult to see as you add rows) and yet, no matter what the size of the sheet at month-end, it will expand to fill (and remain on) a single sheet of paper ???

    One other thing I just noticed....if the last row is already completed, your code inserts a new row but also the data in the row above it...???...shouldn't it be inserting a new 'blank' row??

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    ..thanks....while you were replying, I was editing and added something to my first reply....can you take a look at that as well..thanks.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    <P ID="edit" class=small>(Edited by Jezza on 04-Nov-07 18:46. To change small syntax error)</P>Sorry for the delay, my evening supper got in the way and for some reason the excel workbook kept crashing, I have a feeling it is the repetitive comments in the cells <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    The best way is to set the values of the cells that require input to null, add this just above End Sub


    Range(Cells(LastCell.Row, 1), Cells(LastCell.Row, 4)).Value = ""
    Range(Cells(LastCell.Row, 9), Cells(LastCell.Row, 14)).Value = ""
    Cells(LastCell.Row,16).Value=""
    Cells(LastCell.Row,22).Value=""
    Cells(LastCell.Row,24).Value=""
    Jerry

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    ...OK...that's working very nicely for me...thank you....I will have to play with it for a while, b/c the data on the MONTHLY sheet (see the sample I attached with my 1st post) is copied into corresponding columns on another sheet (teh SUMMARY sheet)...and so I need to figure out how to make the code add a corresponding row to SUMMARY each time I add a row to the MONTHLY.....

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    OK, I think that question is being covered in <post:=674,821>post 674,821</post:>. I think it best the answer goes in that thread to avoid confusion and duplication
    Jerry

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using the + sign to add/insert rows in a works

    actually, it probably isn/t...the post you referred to involves a question about a macro for copying and pasting....Hans has answered me and I have replied to Hans...my question to you about the code that inserts a new row has a 2nd aspect to it, that being that I have to look at your code to see if I can make it work on 2 worksheets (in the same workbook) at the same time, so that as I insert a new row in the MONTHLY sheet, it alos inserts a corresponding new row in the SUMMARY sheet....

Posting Permissions

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