 |
 | 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 / Worksheet Functions / October 2007Duplicating rows help needed
Hi, I've got a huge list that looks like this: ------------------------------- name data 1 data 2 data 3 ------------------------------- book1 2 3 4 book2 4 5 7 book3 5 6 5 I need to duplicate every row (except the heading): book1 2 3 4 book1 2 3 4 book2 4 5 7 book2 4 5 7 Is there a way to automate this routine? You'd save my life... Thanx in advance Assuming source data in Sheet1, cols A to D, from row2 down In Sheet2, Put in A2: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/2),COLUMNS($A:A)-1) Copy A2 across to D2, fill down as far as required  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi, > I've got a huge list that looks like this: [quoted text clipped - 13 lines] > Is there a way to automate this routine? You'd save my life... > Thanx in advance This should do it Sub dupallrows() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 Rows(i).Copy Rows(i).Insert shift:=xlDown Next i End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi, > I've got a huge list that looks like this: [quoted text clipped - 13 lines] > Is there a way to automate this routine? You'd save my life... > Thanx in advance Insert a helper column at the front and then just number every row starting at your first row of data. Just put in 1,2,3 and then select them and double clikc the bottom right of the selection to automatically number downwards. Now just copy the entire set of data and paste it underneath the original. Then just sort on the row numbering you added, and finally delete the additional helper column. Should be 60 seconds max SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- > Hi, > I've got a huge list that looks like this: [quoted text clipped - 13 lines] > Is there a way to automate this routine? You'd save my life... > Thanx in advance |
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.