Row reference in InputBox?

Question

here's the code I currently have in VBA (Excel) at the moment. Most of it has come from macro recordings that I've made. What I'm looking for is to be able to insert for example, row 10 as just 10 in the inputbox without having to put it in as 10:10. Is there a way for me to edit my current code to allow this? I've tried using Rows("TargetRow:TargetRow") but that gives odd results.

Dim TargetRow As Variant
TargetRow = InputBox("Insert row # where data should be inserted. This should take the format XX:XX (e.g. 90:90 for row 90)", "All Industries Row", "XX:XX")

wbThis = ThisWorkbook.Name
Windows(wbThis).Activate
    Rows(TargetRow).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove

Windows("otherworksheet.xlsx").Activate
    Range("A119:J119").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(wbThis).Activate
    Range(TargetRow).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Harun24hr · Accepted Answer · 2017-02-22 04:07:29Z

1

Use following sub to select rows using inputbox

Sub SelectRow()
    Dim lnRow As Long
     lnRow = InputBox("Enter Row number.", "Row Input")
    Rows(lnRow & ":" & lnRow).Select
End Sub

answered Feb 22, 2017 at 4:07

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harun24hr Over a year ago

This sub will select rows taking input from Inputbox() Then adjust this sub with your codes.

Comintern · Accepted Answer · 2017-02-22 04:10:54Z

0

If you need a Range from user input, the simplest way is to use the Excel version Application.InputBox with a type of '8' (see the documentation here).

Dim TargetRow As Range
Set TargetRow = Application.InputBox("Select the row where data should be inserted.", _
                                     "All Industries Row", , , , , , 8)
Debug.Print TargetRow.Address

Note that you should probably also get rid of the Select and Activate calls and use your object references directly.

answered Feb 22, 2017 at 4:10

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Collectives™ on Stack Overflow

Row reference in InputBox?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related