Adding a row using VBA

[hshayhorn]

I have a worksheet that I need to add rows here and there in the worksheet. In a cell I'd like to have a link that runs a macro to add a row above that row. The issue is where this cell lives will obviously change and there will be more than one of these in the worksheet. So the code would need to just run and insert a row above wherever the user is clicking at the time.

 

[khalid_star2005]

I was search for it to and I have this I hope it work for you
Sub New_Formatted_Row_With_Formula()
Dim rActive As Range

Set rActive = ActiveCell

Application.ScreenUpdating = False
With Cells(Rows.Count, "D").End(xlUp)
.EntireRow.Copy
With .Offset(1, 0).EntireRow
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteFormulas
On Error Resume Next
.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
End With

rActive.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[hshayhorn]

Thanks for the reply but that doesn't seem to do the right thing. Run I run that code it adds a row to the bottom. The actual needs is for example...

In row 12 we have a link in B12 that if you click on it adds 1 row above row 12. This link could be clicked multiple times adding multiple rows.

There could be another link in row 20 that does the same thing as the link in row 12. so you click on that one and it adds a row above row 20.

Where ever a link exists and is clicked the row is added before that row. Make sense?

I was search for it to and I have this I hope it work for you
Sub New_Formatted_Row_With_Formula()
Dim rActive As Range

Set rActive = ActiveCell

Application.ScreenUpdating = False
With Cells(Rows.Count, "D").End(xlUp)
.EntireRow.Copy
With .Offset(1, 0).EntireRow
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteFormulas
On Error Resume Next
.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
End With

rActive.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[My Aswer Is This]

You need to use a double click otherwise every time you select a cell a row will be inserted:
With this script when you double click on any cell you will insert a row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Rows(Target.Row).Insert
End Sub

 

[hshayhorn]

Works great!

You need to use a double click otherwise every time you select a cell a row will be inserted:
With this script when you double click on any cell you will insert a row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Rows(Target.Row).Insert
End Sub

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Works great!