 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / July 2008Insert 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. 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  Signature-SA > 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 [quoted text clipped - 32 lines] > > I hope this makes sense. 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. > This goes through and looks at column A and inserts a row where there are > changes. Hope this helps! [quoted text clipped - 55 lines] > > > > I hope this makes sense. 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! Signature-SA > That worked perfectly. Thank you so much. Is there a way to have the inserted > row have a background color ... say yellow maybe? [quoted text clipped - 69 lines] > > > > > > I hope this makes sense. 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 > That worked perfectly. Thank you so much. Is there a way to have the > inserted [quoted text clipped - 76 lines] >> > >> > I hope this makes sense. 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. > That worked perfectly. Thank you so much. Is there a way to have the inserted > row have a background color ... say yellow maybe? [quoted text clipped - 69 lines] > > > > > > I hope this makes sense. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.