Trobule inserting a row through VBA

[Daniel3581]

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.

[Zack Barresse]

Hi Daniel, welcome to VBAX!

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

[Daniel3581]

Hi Zack,

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

Thanks anyway.

[Zack Barresse]

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

[Daniel3581]

The code I used for this was :


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



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

[Zack Barresse]

How about using something like this instead ...

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

Or even better yet ..

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