Consulting

Results 1 to 10 of 10

Thread: Insert rows above designated values

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

    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,940
    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,712
    Location
    Try this macro:


    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

    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,712
    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
  •