Results 1 to 6 of 6

Thread: Insert Rows

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Insert Rows

    Hi I am trying to take a table of Data, then after each change in Column "D" I want to insert 3 blank Rows. Is there anyway to do this? Thanks!

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    Try this:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
         '   only look at single cell changes
        If Target.Count > 1 Then Exit Sub
        Set rng = Columns("D:D")
        '   only look at that range
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        With rng
            Range(Target.Offset(1, 0), Target.Offset(3, 0)).EntireRow.Insert
        End With
    End Sub
    This is a sheet specific event so it needs to go into the specific sheet's
    module.

    Hope that helps,

    Smitty

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    wow...I think I may have asked for more than I can handle. I have no idea what you just put there. I was hoping there was some simple way to do it through the excel menues and functions. I think I will need a website that will teach me the Basics of VB.

  4. #4
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    No problem. That's why everyone is here.

    In your workbook, hit ALT+F11. That will launch the Visual Basic Editor.

    In the left hand pane (that's the Project Explorer), you'll see VBAProject(YourFileName.xls). When you expand it you'll see a folder named Microsoft Excel Objects. Open it and double click on the sheet where you want the rows entered. If none of that is visible, then hit CTRL+R to open the Explorer.

    A blank window will open on the right-hand side. Paste the code that I posted in there and hit ALT+Q to return to Excel.

    Now change/enter something in column D and see what happens.

    The best way to learn this stuff is to start playing with the macro recorder. When you record, VBA will create a folder called Modules. In each instance of Excel, when you record macros, VBA will put them in successive modules. Module1, Module2, etc.

    Just note that the recorder records literally every move that you make so you'll wind up with a lot of unnecessary code that will need to be cleaned for performance.

    Post back with more questions!

    Smitty

    EDIT: here's a website that will give you an introduction to Excel VBA. It's a pretty good tutorial. http://maths.sci.shu.ac.uk/units/ioa/

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Hey thanks for the info. I was waiting for another email telling me there was a response to the forum, I guess I better just check the forum regularly. I will follow your instructions and let you know how it all turns out. Thanks!!

  6. #6
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Anytime!

    I've missed out on a post response or two as well. Don't know if it's the site our the fact that we use Lotus Not(S).

    Smitty

Posting Permissions

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