PDA

View Full Version : Insert row after data change



hwfan123
12-02-2009, 05:37 PM
I need to search through column "A" and when ever the data changes I need to insert a row. For instance I would need a row between 100002447724 and 100002447725.
I am learning the basics of VBA and would like a simple code to do this if anyone can help please.

hwfan123
12-02-2009, 05:39 PM
the numbers listed should be in a column here is an attachment if that helps

GTO
12-02-2009, 06:47 PM
Greetings hwfan123,


I need to search through column "A" and when ever the data changes I need to insert a row.

Okay, that part makes sense.


For instance I would need a row between 100002447724 and 100002447725....

But this part, not so much :think:

Can you describe what would be the factor(s) in determining where a row should get inserted, and what is the change that we are making that needs to initiate this?

Mark

PS - I'm not sure that you can go back and edit your first post after a second, but if the edit button is still available... getting rid of that long string would fix the window.

rbrhodes
12-03-2009, 02:20 AM
hw, try this


Option Explicit

Sub SplitRows()

Dim i As Long
Dim LastRow As Long

'Speed
Application.ScreenUpdating = False

'Get end of data
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Do all from bottom up so insert rows does not change counter 'i'
For i = LastRow To 2 Step -1

'Compare current tow to one above it
If Cells(i, 1) <> Cells(i - 1, 1) Then
'No match = insert a row
Cells(i, 1).EntireRow.Insert
End If
Next i

'Reset

Application.ScreenUpdating = True

End Sub

hwfan123
12-03-2009, 08:13 AM
That worked perfectly thanks so much. That will save a lot of time.