To set up a command that will insert a row, every fifth row?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Excel, how would you set a a command to auto enter a row, every fifth
row of your data sheet?
 
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
 
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
 
shanespop2b said:
Using Excel, how would you set a a command to auto enter a row, every fifth
row of your data sheet?
Click to expand...
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
 
Back
Top