Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    14

    Unanswered: Macro: insert lines at location

    I am trying to create a macro that inserts a few rows after the last full cell. These rows will be copied from another part of the worksheet and contain formulas.
    I found a post that shows how to find the last full cell, but I can't incorporate this into a macro that inserts lines there. Any help is appreciated.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Can you post what you have for the macro right now?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2004
    Posts
    14
    This is what I have so far. I pieced things together from other posts. Not working exactly as I would like.



    Sub test()
    Range("A10:B200").Find("*", ActiveCell, , , , SearchDirection:=xlPrevious).Select

    'moving
    ActiveCell.Offset(1, 0).Select
    Rows("25:34").Select
    Selection.Copy
    Rows("19:19").Select
    Selection.Insert Shift:=xlDown
    End Sub

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Okay. Some questions for you:

    What kind of data do you have in Columns A & B?

    What is the search doing for you?

    What are you hoping to have happen?

    Can you attach a zipped copy of the worksheet?

    Also, when you have lines that end in Select, and the next begins with Select, normally you can combine into one line, like the following:

    Code:
    Sub Example()
        ActiveCell.Offset(1, 0).Select
        Rows("25:34").Copy
        Rows("19:19").Insert Shift:=xlDown
    End Sub
    I have to run right now, but if no one else looks at it, I will tomorrow.

    Just as a note: when you post code, it is easier to read if you use the special tags. (without the spaces).

    [ code ]

    [ /code ]
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Sep 2004
    Posts
    14
    I was hoping not to get too verbose, but here it goes.

    We have a spreadsheet that right now has a couple of hundred lines. In these rows, some of the cells are blank and some have quite lengthy formulas in them waiting to calculate for when we enter stuff into the blank cells(we use this as part of our estimating process).

    The problem is that we don't always use all of the rows so there are a lot of unnessecary blank rows with formulas in them and the file sizes are getting pretty big. What I want to do is to be able to add lines (10 at a time) to the bottom of my spreadsheet. These lines won't be blank because they will have my formulas in them. That way, I will only have the amount of lines I need.

    I cleared out a lot of stuff on the file, but the general idea should be illustrated. Look at the "test" sheet. Let me know if I am unclear.

    Thanks for any help.
    Attached Files Attached Files

Posting Permissions

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