How to insert row on Col A field change
 

I have a SQL table (30+k rows) downloaded via a query and need to insert a
row preceding every change in the MeasID field in Col A. I need to fill in
the new row with data taken from the preceding row (MeasEnd in Col C) copied
to Col B, and following row (MeasStart in Col B) copied to Col C, and set
this new row's Value in Col D to "0". I need to do this new row insertion
for the entire SQL table. I'd appreciate your assistance on this. TIA.
Here is an extract of the SQL table:

MeasID MeasStart MeasEnd Value

905 6/30/2009 5:48 6/30/2009 5:50 32
907 6/30/2009 5:59 6/30/2009 5:59 25
907 6/30/2009 5:59 6/30/2009 6:04 32
907 6/30/2009 6:04 6/30/2009 6:05 53
907 6/30/2009 6:05 6/30/2009 7:13 39
909 6/30/2009 23:39 6/30/2009 23:40 25
909 6/30/2009 23:40 7/1/2009 0:24 32
909 7/1/2009 0:24 7/1/2009 0:24 39
909 7/1/2009 0:24 7/1/2009 2:59 53
911 7/1/2009 3:11 7/1/2009 3:12 25
911 7/1/2009 3:12 7/1/2009 5:13 32
913 7/1/2009 5:21 7/1/2009 5:22 18

How to insert row on Col A field change
 

Try this macro:

Sub AAA()
StartRow = 3 ' Change to suit
TargetColumn = "A" ' Change to suit

LastRow = Range(TargetColumn & StartRow).End(xlDown).Row
For r = LastRow To StartRow + 1 Step -1
If Range(TargetColumn & r).Value < Range(TargetColumn & r - 1) Then
Rows(r).Insert
Range("C" & r - 1).Copy Range("B" & r)
Range("B" & r + 1).Copy Range("C" & r)
Range("D" & r) = 0
End If
Next
Application.CutCopyMode = False
End Sub

Regards,
Per

"Cinco" skrev i meddelelsen
...
I have a SQL table (30+k rows) downloaded via a query and need to insert a
row preceding every change in the MeasID field in Col A. I need to fill
in
the new row with data taken from the preceding row (MeasEnd in Col C)
copied
to Col B, and following row (MeasStart in Col B) copied to Col C, and set
this new row's Value in Col D to "0". I need to do this new row
insertion
for the entire SQL table. I'd appreciate your assistance on this. TIA.
Here is an extract of the SQL table:

MeasID MeasStart MeasEnd Value

905 6/30/2009 5:48 6/30/2009 5:50 32
907 6/30/2009 5:59 6/30/2009 5:59 25
907 6/30/2009 5:59 6/30/2009 6:04 32
907 6/30/2009 6:04 6/30/2009 6:05 53
907 6/30/2009 6:05 6/30/2009 7:13 39
909 6/30/2009 23:39 6/30/2009 23:40 25
909 6/30/2009 23:40 7/1/2009 0:24 32
909 7/1/2009 0:24 7/1/2009 0:24 39
909 7/1/2009 0:24 7/1/2009 2:59 53
911 7/1/2009 3:11 7/1/2009 3:12 25
911 7/1/2009 3:12 7/1/2009 5:13 32
913 7/1/2009 5:21 7/1/2009 5:22 18

How to insert row on Col A field change
 

Per,

Your macro worked like a charm - it did everything that I requested help
with! Thanks - you're awesome. We newbies really appreciate all the help
that we receive from this website.

Jim

"Per Jessen" wrote:

Try this macro:

Sub AAA()
StartRow = 3 ' Change to suit
TargetColumn = "A" ' Change to suit

LastRow = Range(TargetColumn & StartRow).End(xlDown).Row
For r = LastRow To StartRow + 1 Step -1
If Range(TargetColumn & r).Value < Range(TargetColumn & r - 1) Then
Rows(r).Insert
Range("C" & r - 1).Copy Range("B" & r)
Range("B" & r + 1).Copy Range("C" & r)
Range("D" & r) = 0
End If
Next
Application.CutCopyMode = False
End Sub

Regards,
Per

"Cinco" skrev i meddelelsen
...
I have a SQL table (30+k rows) downloaded via a query and need to insert a
row preceding every change in the MeasID field in Col A. I need to fill
in
the new row with data taken from the preceding row (MeasEnd in Col C)
copied
to Col B, and following row (MeasStart in Col B) copied to Col C, and set
this new row's Value in Col D to "0". I need to do this new row
insertion
for the entire SQL table. I'd appreciate your assistance on this. TIA.
Here is an extract of the SQL table:

MeasID MeasStart MeasEnd Value

905 6/30/2009 5:48 6/30/2009 5:50 32
907 6/30/2009 5:59 6/30/2009 5:59 25
907 6/30/2009 5:59 6/30/2009 6:04 32
907 6/30/2009 6:04 6/30/2009 6:05 53
907 6/30/2009 6:05 6/30/2009 7:13 39
909 6/30/2009 23:39 6/30/2009 23:40 25
909 6/30/2009 23:40 7/1/2009 0:24 32
909 7/1/2009 0:24 7/1/2009 0:24 39
909 7/1/2009 0:24 7/1/2009 2:59 53
911 7/1/2009 3:11 7/1/2009 3:12 25
911 7/1/2009 3:12 7/1/2009 5:13 32
913 7/1/2009 5:21 7/1/2009 5:22 18