How to insert row on Col A field change


C

Cinco

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
 
Ad

Advertisements

P

Per Jessen

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
 
Ad

Advertisements

C

Cinco

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top