If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
To set up a command that will insert a row, every fifth row?
Using Excel, how would you set a a command to auto enter a row, every fifth
row of your data sheet? |
#2
|
|||
|
|||
shanespop2b, here is some code that will do it, original code by Dave
Peterson, modified to your needs Sub Add_Row() 'will add a row after every 5th row 'By: Dave Peterson Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = 5 To LastRow Step 5 If myRng Is Nothing Then Set myRng = .Cells(iCtr, "A") Else Set myRng = Union(.Cells(iCtr, "A"), myRng) End If Next iCtr End With If myRng Is Nothing Then 'do nothing Else myRng.EntireRow.Insert End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "shanespop2b" wrote in message ... Using Excel, how would you set a a command to auto enter a row, every fifth row of your data sheet? |
#3
|
|||
|
|||
To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open the VBA editor, in the left hand window click on your workbook name, go to insert, module, and paste the code in the window that opens on the right hand side, press Alt and Q to close this window and go back to your workbook and press alt and F8, this will bring up a box to pick the Macro from, click on the Macro name to run it. If you are using excel 2000 or newer you may have to change the macro security settings to get the macro to run. To change the security settings go to tools, macro, security, security level and set it to medium -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Paul B" wrote in message ... shanespop2b, here is some code that will do it, original code by Dave Peterson, modified to your needs Sub Add_Row() 'will add a row after every 5th row 'By: Dave Peterson Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = 5 To LastRow Step 5 If myRng Is Nothing Then Set myRng = .Cells(iCtr, "A") Else Set myRng = Union(.Cells(iCtr, "A"), myRng) End If Next iCtr End With If myRng Is Nothing Then 'do nothing Else myRng.EntireRow.Insert End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "shanespop2b" wrote in message ... Using Excel, how would you set a a command to auto enter a row, every fifth row of your data sheet? |
#4
|
|||
|
|||
shanespop2b wrote:
Using Excel, how would you set a a command to auto enter a row, every fifth row of your data sheet? You might want to take a look at ASAP Utilities. It's a free program that has MANY useful built in functions. For example for the above you can do columns or rows by just picking the color and then a number for every nth row or column. Makes it really easy. Here's the link. No ads or pop ups. http://www.asap-utilities.com/ Standard disclaimer: no association of any sort with ASAP Utilities. I just find it very useful. gls858 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cannot insert Pictures again | Peter | Using Forms | 0 | April 28th, 2005 11:39 AM |
Cannot insert Pictures again | Peter | Using Forms | 0 | April 28th, 2005 11:37 AM |
Insert picture with VBA | wschiro | Powerpoint | 2 | April 25th, 2005 06:14 AM |
Increment column value based on insert string | A.W.J. Ales | Worksheet Functions | 1 | April 15th, 2004 02:44 PM |