Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write Macro to Fix Data Alignment (Data dump from Crystal to Excel | Excel Discussion (Misc queries) | |||
change colorIndex, help me write an easy macro | Excel Programming | |||
how do i write a macro to change font color with live data? | Excel Programming | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
How to read a SQL Table into Excel change the data and write back into SQL | Excel Programming |