Consulting

Results 1 to 10 of 10

Thread: Solved: Insert rows above designated values

  1. #1
    VBAX Newbie
    Joined
    Aug 2004
    Posts
    4
    Location

    Solved: Insert rows above designated values

    In column H of the attached workbook is a #. Each time the number changes going down the column I want to insert a row and the next incremental number in column A. My desired end result can be seen on page 2 at www.vbaexpress.com/EE/Example.pdf. Thanks for any help, I appreciate it.

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    In your pdf file, what is column I, column H and column A? What are the red arrows pointing to, is that A?

  3. #3
    VBAX Newbie
    Joined
    Aug 2004
    Posts
    4
    Location
    Column A is the # that the red arrows are pointing to, H is th column with the # all the way to the right. There isn't really a column I in there.

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Try this macro:

    [vba]
    Option Explicit

    Sub SeperateData()

    Dim x As Long
    Dim LastRow As Long
    Dim Index As Long

    With Sheets("Sheet1") 'Change to the actual sheet name.
    LastRow = .Range("H65536").End(xlUp).Row
    Index = Range("H" & LastRow).Value
    For x = LastRow To 2 Step -1 'Change 2 to the row you want to start at.
    If .Range("H" & x).Value = Index Then
    'Skip
    Else
    Index = Range("H" & x).Value
    Range("A" & x + 1).Value = Index + 1
    Range("A" & x + 1).EntireRow.Insert
    End If
    Next x
    End With

    End Sub
    [/vba]

    Open your Excel file.
    Press Alt + F11 to open the VBE.
    Insert | Module.
    Paste the code there.
    Close the VBE.
    From Excel Tools | Macro | Macros.
    Select SeperateData and press Run.

    If it is not working properly, please post an example of the Excel file, since it is difficult to work with a PDF file.

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    OMG, Jake. That's gorgeous.
    ~Anne Troy

  6. #6

  7. #7
    VBAX Newbie
    Joined
    Aug 2004
    Posts
    4
    Location

    Excel file

    This is the sheet that I'm working with. I tried that macro and it didn't really work. Thanks for helping.

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    What DID happen, Ed?
    ~Anne Troy

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    I just tried and it seems to work for me. See attached.

  10. #10
    VBAX Newbie
    Joined
    Aug 2004
    Posts
    4
    Location
    Sorry it took me so long to get back, I've been busy with some other work and haven't had a whole lot of time macro, but I did finally get what I was looking for. Thanks for the help.

Posting Permissions

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