 |
 | 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 / New Users / March 2008Insert a blank row between each used row in document
I need a macro to insert a blank row after each row in my document; that is, every other row will be an inserted row. For example: I have: Row 4 Smith, James P Maintenance $32.75 12 8 4 6 9 ... Row 5 Mason, Carole Admin $18.56 10 8 10 5 0 ... and I need Row 4 Smith, James P Maintenance $32.75 12 8 4 6 9 ... Row 5 Blank row Row 6 Mason, Carole Admin $18.75 10 8 10 5 0 ... Row 7 Blank row for the entire document (number of entries changes). Thanks. One way is to insert a helper column to the left of your data, then fill down a number series (1,2,3,etc) next to each row of data. Copy that number series and paste it immediately below itself. Then sort on that column. You can then delete the helper column. Maybe this thread will help... http://tinyurl.com/2efccx HTH, JP > I need a macro to insert a blank row after each row in my document; > that is, every other row will be an inserted row. [quoted text clipped - 18 lines] > > for the entire document (number of entries changes). Thanks. One way of doing it without a macro is to fill a sequence 1, 2, 3 etc down an empty column for as many rows as you currently have (suppose this is 300). Then copy that sequence down into the next 300 (blank) cells in the same column. Then you can highlight all 600 rows and sort them using the sequence number as the key field. You can then delete the sequence column. Hope this helps. Pete > I need a macro to insert a blank row after each row in my document; > that is, every other row will be an inserted row. [quoted text clipped - 18 lines] > > for the entire document (number of entries changes). Thanks. It is truly disturbing how similar our replies are. *slowly backing away from computer* :-) --JP > One way of doing it without a macro is to fill a sequence 1, 2, 3 etc > down an empty column for as many rows as you currently have (suppose [quoted text clipped - 32 lines] > > - Show quoted text - Yes, I thought that when I read yours - and only 1 minute difference between the posts, too <bg> Pete > It is truly disturbing how similar our replies are. > [quoted text clipped - 42 lines] > > - Show quoted text - > Yes, I thought that when I read yours - and only 1 minute difference > between the posts, too <bg> [quoted text clipped - 49 lines] > > - Show quoted text - Thanks, guys. Your suggestions are very similar and will work very well for my purpose. I appreciate the help. If you really want a macro solution, then you can turn the macro recorder on while you do this once (use relative addressing). amd then in future you can just re-run the macro when required. Hope this helps. Pete > Thanks, guys. Your suggestions are very similar and will work very > well for my purpose. I appreciate the help Here is a macro that inserts a blank row between each row of data - courtesy of a previous contributor on this group (sorry but I cannot recall their name at present). Sub AddRows() Dim r As Long Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False '\***You can change LastRow 1 below to stop at any row number *** For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) <> 0 Then Rows(r + 1).Insert Shift=xlDown Next r End Sub -- Fred
|
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.