Same format as below in excel Hi, I want to add a row whenever I need, so I made

Question

Same format as below in excel

Hi,

I want to add a row whenever I need, so I made input box to do that.

My question is how I can format exactly same as below row.

I need formulas and formats but not contents.

Below is my code;

nRow = InputBox("Select a row you want to add above")

If nRow <> "" Then

    r = nRow
    Rows(r).EntireRow.Insert shift:=xlDown
    Rows(r + 1).Copy
   
    Rows(r).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
   
    Rows(r).SpecialCells(xlConstants).Clear
Else

Exit Sub
End If

In this row, some columns are formatted as currency , center alignment, or red font color, and so on.

I'd like to format exactly same as below, but I couldn't .

Thanks

Explanation / Answer

^Try the following adaptation of your code:@@

'==========>>
Option Explicit

'---------->>
Public Sub Tester()
    Dim SH As Worksheet
    Dim r As Long
    Dim nRow As Variant

    Set SH = ActiveSheet

    nRow = InputBox("Select a row you want to add above")
    If nRow <> "" Then
        r = nRow
        With SH
            .Rows(r).EntireRow.Insert shift:=xlDown
            .Rows(r + 1).Copy Destination:=.Rows(r)
            .Rows(r).SpecialCells(xlConstants).ClearContents
        End With
    End If
End Sub
'<<==========

===^_^=====

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.