Create rows and merge the first three columns-CodePudding

I'd like to have button in excel that insert a row and then merge the first three columns as well.

below is my code. It makes the row but it doesn not merge the columns. I just started VBA today so I assume it might be a syntax error.

Can someone assist pls? Cheers

my vba code:

Sub AddRow()
Dim rowNum As Integer
    On Error Resume Next
    rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                    Title:="VCRM")
    Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown
    Range("A(rowNum):A(rowNum   1)").Merge False
End Sub

CodePudding user response：

You can try this one too. This function accepts only numeric values for the row number. If you enter any other character, the pop up box will say, "Number is not valid", and the InputBox will stay and not end the function until you enter a number (you can click on x if you want to cancel).

Sub add_rows()
    row_number = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                    Title:="VCRM", Type:=1)
    ThisWorkbook.Sheets("Sheet1").Rows(row_number).Insert
    Rng = "A" & row_number & ":" & "C" & row_number
    ThisWorkbook.Sheets("Sheet1").Range(Rng).Merge
    ThisWorkbook.Sheets("Sheet1").Range(Rng).HorizontalAlignment = xlCenter
End Sub

CodePudding user response：

Something like this:

Sub AddRow()
    Dim ws As Worksheet, rowNum As Long 'use Long instead of Integer
    On Error Resume Next 'ignore error if user doesn't enter a number
    rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                    Title:="VCRM")
    On Error GoTo 0 'stop ignoring errors
    If rowNum = 0 Then
        MsgBox "A numeric value is required!", vbExclamation
        Exit Sub
    End If
    
    Set ws = ActiveSheet
    ws.Rows(rowNum).Insert Shift:=xlDown
    ws.Cells(rowNum, "A").Resize(1, 3).Merge
End Sub