View Full Version : Trobule inserting a row through VBA
Daniel3581
02-27-2006, 07:44 PM
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
02-27-2006, 09:09 PM
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
02-27-2006, 09:46 PM
Hi Zack,
Thanks for your response, but I have just managed to solve the problem.
Thanks anyway.
Zack Barresse
02-27-2006, 10:39 PM
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
02-28-2006, 01:33 AM
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
02-28-2006, 09:55 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.