Trobule inserting a row through VBA

Hi All,

I'm having a bit of a problem with inserting an entire row when values in a row are identical. The problem is: I have a list of numbers in column 2 (sorted in ascending order). I need to insert a column between values that are different, whilst keeping the others "grouped" by blank rows. :dunno

If anyone could provide any help, it would be much appreciated.

Thanks.

Hi Daniel, welcome to VBAX!

Do you think you could upload a small scaled down example of your file? Or give a few examples?

Hi Zack,

Thanks for your response, but I have just managed to solve the problem.

Thanks anyway.

Ah, ok. Would you mind posting how you solved the problem, so that others who search the forums will get the benefit if your hard work? :)

Don't forget, you can mark your own threads as solved by going to Thread Tools | Mark Thread Solved | Perform Action. :yes

The code I used for this was :


[vba]Sub InsertHeader()

Dim X as long, EndRow as long
Dim ID1 as string, ID2 as string

Range(Cells(65536, 2), Cells(65536, 2).End.xlUp)).Select
EndRow=Selection.Row

ID1=Range("B2")
ID2=""

For X = 1 to EndRow
If ID1 <> ID2 then
Cells(X, 2).Select
Selection.EntireRow.Insert
ActiveCell.OffSet(1, 0).Select
X=X+1
End If
ID1=ID2
ActiveCell.Offset(1, 0).Select
ID1 = ActiveCell.Value
Next X

End Sub[/vba]



.....Now, to look for that solved button:think:

How about using something like this instead ...

[vba]Sub InsertHeader_x2()

Dim X As Long, EndRow As Long
Dim ID2 As String

EndRow = Cells(Rows.Count, 2).End(xlUp).Row
ID2=""

For X = EndRow To 1 Step -1
If Cells(X, 2) <> ID2 Then Cells(X, 2).EntireRow.Insert
Next X

End Sub[/vba]

Or even better yet ..

[vba]Sub InsertHeader_x3()

Dim X As Long, ID2 As String
For X = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
If Cells(X, 2) <> ID2 Then Cells(X, 2).EntireRow.Insert
Next X

End Sub[/vba]