Clear Contents in an insert rows x number of times Macro

**Rachael Whyment** · 05-11-2013, 08:11 AM

I have a macro that inserts a number of rows (input by the user) at the next available row, I just need this to also clear the contents. I tried inserting Selection.ClearContents into the code but I'm new to this and wasn't sure where to put it. Any help much appreciated:

Private Sub CommandButton15_Click()
Dim lngRows As Long, lngNextRow As Long

On Error GoTo Finish

lngRows = CLng(InputBox("How many rows do you wish to insert?"))
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

Rows(43).Copy Rows(lngNextRow & ":" & lngNextRow + lngRows - 1)

Finish:
If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"

End Sub

Thanks in advance

**HaHoBe** · 05-11-2013, 09:52 AM

Hi, Rachael Whyment,

maybe just paste the formats over?

Private Sub CommandButton15_Click()
Dim lngRows As Long, lngNextRow As Long

On Error GoTo Finish

lngRows = CLng(InputBox("How many rows do you wish to insert?"))
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

Rows(43).Copy
Rows(lngNextRow & ":" & lngNextRow + lngRows - 1).PasteSpecial Paste:=xlPasteFormats

Finish:
If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"

End Sub

Private Sub CommandButton15_Click()
Dim lngRows As Long, lngNextRow As Long

On Error GoTo Finish

lngRows = CLng(InputBox("How many rows do you wish to insert?"))
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

Rows(43).Copy Rows(lngNextRow & ":" & lngNextRow + lngRows - 1)
Rows(lngNextRow & ":" & lngNextRow + lngRows - 1).ClearContents

Finish:
If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"

End Sub

Ciao,
Holger