View Full Version : Solved: Insert rows above designated values

08-27-2004, 03:34 PM
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 (http://www.vbaexpress.com/EE/Example.pdf). Thanks for any help, I appreciate it.:help

Zack Barresse
08-27-2004, 03:48 PM
In your pdf file, what is column I, column H and column A? What are the red arrows pointing to, is that A?

08-27-2004, 03:57 PM
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.

Jacob Hilderbrand
08-27-2004, 03:58 PM
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
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.

Anne Troy
08-27-2004, 04:08 PM
OMG, Jake. That's gorgeous. :D

Jacob Hilderbrand
08-27-2004, 04:11 PM
Thanks :)

08-27-2004, 04:30 PM
This is the sheet that I'm working with. I tried that macro and it didn't really work. Thanks for helping.

Anne Troy
08-27-2004, 04:44 PM
What DID happen, Ed?

Jacob Hilderbrand
08-27-2004, 04:45 PM
I just tried and it seems to work for me. See attached.

09-08-2004, 08:50 AM
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.