- Code: Select all
Public Sub AddRow(Optional ByVal Rt As Long, _
Optional ByVal Rs As Long, _
Optional ByRef Ws As Worksheet)
If Ws Is Nothing Then Set Ws = ActiveSheet
If Rt < 1 Then Rt = LastRow(1, Ws) + 1
If Rs < 1 Then Rs = Rt - IIf(Rt = 1, 0, 1)
With Ws
.Rows(Rs).Copy
.Rows(Rt).Insert xlShiftDown
Application.CutCopyMode = False
On Error Resume Next
With .Rows(Rt)
.Cells.SpecialCells(xlCellTypeConstants).ClearContents
.Cells(1).Select
End With
End With
End Sub
Paste it in the same module as the above procedure, which should be a normal code sheet, called 'Module1' by default. You can change the name.http://www.excelkey.com/forum/viewtopic.php?f=5&t=393
AddRow's syntax is very simple:
- Code: Select all
AddRow [Target_Row,] [Source_Row, [Worksheet]
- Ws = Worksheet
If you don't furnish this argument the procedure will work on the active worksheet. Unless you are running a complicated project where you add rows on a worksheet that you don't want to activate just yet the currently active worksheet will suit your requirements just fine and you don't need to specify anything. - Rs = Source Row
I often want formats and formulas copied from the first row. The argument is that, if I ever want to introduce changes I just have to introduce them in the first row and all new rows will take them from there. If this is what you want, just supply the number of your first row to the procedure. Remember that arguments must be supplied in the sequence of the parameters. If you want to supply Rs but don't supply Rt you must pass the argument as a named argument, like this AddRow Rs:=3. If you don't want to supply Rs or supply Rs<1 the procedure will copy from the last previous row in the worksheet. I have found that the logic of why to copy from the first row applies to the last row equally well. - Rt = Target Row
You may not wish to add the new row at the bottom of your worksheet which is the default. In some projects you prefer to add new rows at the top of the list or at some calculated location in your worksheet. By specifying Rt you can add your new row anywhere you want. If you specify, say, 12 a new row No. 12 will be inserted. The row that used to be No. 12 will become row 13. Unless you specified another source, formats and formulas will be copied from row 11 which will continue to be row 11. - On Error Resume Next
The source row is copied with all its contents. Then, in the copy, all contents except formulas is deleted. If there was nothing to delete because all cells were already blank an error occurs. The On Error Resume Next statement ensures that you never notice. - .Cells(1).Select
This line selects the first cell in the new row. If you don't want that cell selected, delete the line or change the cell. Cells(1) is in column A, Cells(2) in column B etc. If you have inserted the row in a worksheet other than the active one the cell will not be selected and an error occurs. However, due to the preceding Resume Next statement your program will continue running. If you wish to select a cell on a worksheet which isn't active you should activate the worksheet first. Use this code to do that: Ws.Activate
There are many meaningful ways to call the above procedure. You may like the simplest one of all. Paste this procedure in the code sheet of the worksheet where you want the blank rows inserted:
- Code: Select all
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Cells(LastRow + 1, 1).Address Then
AddRow
End If
End Sub
As you see, this procedure also calls the LastRow function already mentioned above. Make sure that it is declared as 'Public' (it is 'Private' when you copy it from this site and need not be 'Public' if you only call it from the other module).