PDA

View Full Version : [SOLVED] Add 1 row below a selected cell using a user input box



gtbreps_2
01-10-2017, 08:58 AM
I'm trying to create a user input where they can select a cell in column B that acts like a sub heading and it will insert a row below that cell. The input box is a range object and I can't seem to figure out how to move my range down 1 row to then execute an insert row function like one of these:

Rows("3:5").EntireRow.Insert

or

Range("A2").EntireRow.Insert

I'd assume because I'm dealing with x being a range that I'd try to somehow move my range down 1 row then execute a Range("A2").EntireRow.Insert function, but again, I don't know how to jump to the line below my range "x" that I selected in the Input Box.


Sub AddScope_Click()


Dim x As Range
On Error Resume Next
Application.DisplayAlerts = False
Set x = Application.InputBox(Prompt:="Select Trade to Add Scope Item Under", _
Title:="Add Scope Item", _
Default:="Select Trade", _
Type:=8)
Application.DisplayAlerts = True

If x Is Nothing Then
Exit Sub
Else
'pick row 1 down from range "x" and insert row there
End If
End Sub

GTO
01-10-2017, 09:06 AM
Try using .Offset, something like:



Sub example()
Dim rngSelected As Range

Set rngSelected = Application.InputBox(Prompt:="Select Trade to Add Scope Item Under", _
Title:="Add Scope Item", _
Default:="Select Trade", _
Type:=8)

If Not rngSelected Is Nothing Then
rngSelected.Offset(1).EntireRow.Insert Shift:=xlShiftDown
End If

End Sub


Hope that helps,

Mark

gtbreps_2
01-10-2017, 09:11 AM
That's exactly what I was looking for. Thanks Mark

GTO
01-10-2017, 09:19 AM
You are very welcome:)