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!!

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!!

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
(E-Mail Removed)
"Kevin1061" <(E-Mail Removed)> wrote in message
news:9548C051-2E1F-460C-8107-(E-Mail Removed)...
> 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!!

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
> (E-Mail Removed)
> "Kevin1061" <(E-Mail Removed)> wrote in message
> news:9548C051-2E1F-460C-8107-(E-Mail Removed)...
> > 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!!
>
>

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Kevin1061" <(E-Mail Removed)> wrote in message
news:80174169-2BF3-457C-B32B-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Kevin1061" <(E-Mail Removed)> wrote in message
>> news:9548C051-2E1F-460C-8107-(E-Mail Removed)...
>> > 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!!
>>
>>