Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row After Series
I think this is possible but I'm not certain of the way the code would look.
I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row After Series
This goes through and looks at column A and inserts a row where there are
changes. Hope this helps! Option Explicit Sub insertrows() Dim curselection As Range Set curselection = Range("A1") 'or wherever you start Do While curselection < "" If curselection < curselection.Offset(1, 0) Then curselection.Offset(1, 0).EntireRow.Insert Set curselection = curselection.Offset(1, 0) End If Set curselection = curselection.Offset(1, 0) Loop End Sub -- -SA "FrankM" wrote: I think this is possible but I'm not certain of the way the code would look. I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row After Series
That worked perfectly. Thank you so much. Is there a way to have the inserted
row have a background color ... say yellow maybe? I tried modifying the line curselection.Offset(1, 0).EntireRow.Insert to include Interior.ColorIndex = 41 so it was curselection.Offset(1, 0).EntireRow.Insert.Interior.ColorIndex = 41 but I got an error, I'm not surprised. It was a long shot but I thought it was worth trying. Any ideas would be great. Thanks again. "StumpedAgain" wrote: This goes through and looks at column A and inserts a row where there are changes. Hope this helps! Option Explicit Sub insertrows() Dim curselection As Range Set curselection = Range("A1") 'or wherever you start Do While curselection < "" If curselection < curselection.Offset(1, 0) Then curselection.Offset(1, 0).EntireRow.Insert Set curselection = curselection.Offset(1, 0) End If Set curselection = curselection.Offset(1, 0) Loop End Sub -- -SA "FrankM" wrote: I think this is possible but I'm not certain of the way the code would look. I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row After Series
You could do:
curselection.Offset(1, 0).EntireRow.Insert curselection.Offset(1, 0).EntireRow.Select Selection.Interior.ColorIndex = 41 '41 is blue yellow is 6 Set curselection = curselection.Offset(1, 0) Hope this helps! -- -SA "FrankM" wrote: That worked perfectly. Thank you so much. Is there a way to have the inserted row have a background color ... say yellow maybe? I tried modifying the line curselection.Offset(1, 0).EntireRow.Insert to include Interior.ColorIndex = 41 so it was curselection.Offset(1, 0).EntireRow.Insert.Interior.ColorIndex = 41 but I got an error, I'm not surprised. It was a long shot but I thought it was worth trying. Any ideas would be great. Thanks again. "StumpedAgain" wrote: This goes through and looks at column A and inserts a row where there are changes. Hope this helps! Option Explicit Sub insertrows() Dim curselection As Range Set curselection = Range("A1") 'or wherever you start Do While curselection < "" If curselection < curselection.Offset(1, 0) Then curselection.Offset(1, 0).EntireRow.Insert Set curselection = curselection.Offset(1, 0) End If Set curselection = curselection.Offset(1, 0) Loop End Sub -- -SA "FrankM" wrote: I think this is possible but I'm not certain of the way the code would look. I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row After Series
Add this line...
curselection.Offset(1, 0).EntireRow.Interior.ColorIndex = 41 right after this line... curselection.Offset(1, 0).EntireRow.Insert although ColorIndex 41 is not yellow on my system (it's blue). Rick "FrankM" wrote in message ... That worked perfectly. Thank you so much. Is there a way to have the inserted row have a background color ... say yellow maybe? I tried modifying the line curselection.Offset(1, 0).EntireRow.Insert to include Interior.ColorIndex = 41 so it was curselection.Offset(1, 0).EntireRow.Insert.Interior.ColorIndex = 41 but I got an error, I'm not surprised. It was a long shot but I thought it was worth trying. Any ideas would be great. Thanks again. "StumpedAgain" wrote: This goes through and looks at column A and inserts a row where there are changes. Hope this helps! Option Explicit Sub insertrows() Dim curselection As Range Set curselection = Range("A1") 'or wherever you start Do While curselection < "" If curselection < curselection.Offset(1, 0) Then curselection.Offset(1, 0).EntireRow.Insert Set curselection = curselection.Offset(1, 0) End If Set curselection = curselection.Offset(1, 0) Loop End Sub -- -SA "FrankM" wrote: I think this is possible but I'm not certain of the way the code would look. I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row After Series
Thank you. Both Rick and SA you both helped me out quite a bit. I really
appreciate it. Worked like a charm. Yes I know the ColorIndex in my example was Blue not Yellow, I was just picking random colors just for examples. Thank you for pointing me in the right direction. I am very grateful. "FrankM" wrote: That worked perfectly. Thank you so much. Is there a way to have the inserted row have a background color ... say yellow maybe? I tried modifying the line curselection.Offset(1, 0).EntireRow.Insert to include Interior.ColorIndex = 41 so it was curselection.Offset(1, 0).EntireRow.Insert.Interior.ColorIndex = 41 but I got an error, I'm not surprised. It was a long shot but I thought it was worth trying. Any ideas would be great. Thanks again. "StumpedAgain" wrote: This goes through and looks at column A and inserts a row where there are changes. Hope this helps! Option Explicit Sub insertrows() Dim curselection As Range Set curselection = Range("A1") 'or wherever you start Do While curselection < "" If curselection < curselection.Offset(1, 0) Then curselection.Offset(1, 0).EntireRow.Insert Set curselection = curselection.Offset(1, 0) End If Set curselection = curselection.Offset(1, 0) Loop End Sub -- -SA "FrankM" wrote: I think this is possible but I'm not certain of the way the code would look. I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Selectable Series and Number of Series for Line Chart | Charts and Charting in Excel | |||
Fill Series Dates: not letting me change the series from year to m | Excel Discussion (Misc queries) | |||
Excel : insert new data series via mouse click on graphic. | Charts and Charting in Excel | |||
series graph -- one series being added to another series | Charts and Charting in Excel | |||
Filling in a Date Series using the Fill | Series menu command | Excel Programming |