Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert row and automatically generate field in new row | Excel Discussion (Misc queries) | |||
Unable to change field settings in calculated field in a pivot tab | Excel Discussion (Misc queries) | |||
Insert Function field? | Excel Discussion (Misc queries) | |||
Extract last name and insert into a different field. | Excel Worksheet Functions | |||
can i insert the name of the worksheet into a field? | Excel Discussion (Misc queries) |