Can I write a macro to insert a row after a data change?
 

After running a database query, the default output is exported to an Excel
file. The format is such that there are unique column headers, and there are
multiple rows for each "record" because there is a unique record in a column
on each row.

Column A is for an ID number. Then there may be 6 rows of the same ID
number because there is a unique record value in Column H for that ID number.
So, Column A has perhaps 6 or 7 rows of the same ID number, before the next
ID number is shown.

I need to find a way to write a macro or VB to query Column A and insert a
row when there is a change in data. For example, when the ID number changes
from 1 to 2, or from "1-3" to "1-4".

Any suggestions?
Thanks!!

Can I write a macro to insert a row after a data change?
 

This may work as long as the ID numbers go in ascending order

Sub untested()
Dim rngID As Range
Dim i As Long
Set rngID = ActiveSheet.Range(Cells(2, "A"), _
Cells(Rows.Count, "A").End(xlUp))
With rngID
For i = .Rows.Count To 1 Step -1
If .Cells(i) .Cells(i).Offset(-1, 0) Then
.Cells(i).EntireRow.Insert
End If
Next i
End With
End Sub
"Kevin1061" wrote:

After running a database query, the default output is exported to an Excel
file. The format is such that there are unique column headers, and there are
multiple rows for each "record" because there is a unique record in a column
on each row.

Column A is for an ID number. Then there may be 6 rows of the same ID
number because there is a unique record value in Column H for that ID number.
So, Column A has perhaps 6 or 7 rows of the same ID number, before the next
ID number is shown.

I need to find a way to write a macro or VB to query Column A and insert a
row when there is a change in data. For example, when the ID number changes
from 1 to 2, or from "1-3" to "1-4".

Any suggestions?
Thanks!!

Can I write a macro to insert a row after a data change?
 

This should do it
Sub insertrowsforeachacct()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kevin1061" wrote in message
...
After running a database query, the default output is exported to an Excel
file. The format is such that there are unique column headers, and there
are
multiple rows for each "record" because there is a unique record in a
column
on each row.

Column A is for an ID number. Then there may be 6 rows of the same ID
number because there is a unique record value in Column H for that ID
number.
So, Column A has perhaps 6 or 7 rows of the same ID number, before the
next
ID number is shown.

I need to find a way to write a macro or VB to query Column A and insert a
row when there is a change in data. For example, when the ID number
changes
from 1 to 2, or from "1-3" to "1-4".

Any suggestions?
Thanks!!

Can I write a macro to insert a row after a data change?
 

Thanks to all! These helped a bunch!

Kevin


"Don Guillett" wrote:

This should do it
Sub insertrowsforeachacct()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kevin1061" wrote in message
...
After running a database query, the default output is exported to an Excel
file. The format is such that there are unique column headers, and there
are
multiple rows for each "record" because there is a unique record in a
column
on each row.

Column A is for an ID number. Then there may be 6 rows of the same ID
number because there is a unique record value in Column H for that ID
number.
So, Column A has perhaps 6 or 7 rows of the same ID number, before the
next
ID number is shown.

I need to find a way to write a macro or VB to query Column A and insert a
row when there is a change in data. For example, when the ID number
changes
from 1 to 2, or from "1-3" to "1-4".

Any suggestions?
Thanks!!

Can I write a macro to insert a row after a data change?
 

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kevin1061" wrote in message
...
Thanks to all! These helped a bunch!

Kevin


"Don Guillett" wrote:

This should do it
Sub insertrowsforeachacct()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Insert
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kevin1061" wrote in message
...
After running a database query, the default output is exported to an
Excel
file. The format is such that there are unique column headers, and
there
are
multiple rows for each "record" because there is a unique record in a
column
on each row.

Column A is for an ID number. Then there may be 6 rows of the same ID
number because there is a unique record value in Column H for that ID
number.
So, Column A has perhaps 6 or 7 rows of the same ID number, before the
next
ID number is shown.

I need to find a way to write a macro or VB to query Column A and
insert a
row when there is a change in data. For example, when the ID number
changes
from 1 to 2, or from "1-3" to "1-4".

Any suggestions?
Thanks!!