1

I have the following code and i want to make sure that the last row after copying a row n times highlights/selects the nth row.

Example: i copied 11 rows i want to highlight/select the 11th row instead of the whole range copied

Sub test2()
Dim n As Integer, rng As Range

    'new section >>
    On Error GoTo EH
    Set rng = Application.InputBox("Select any cell/cells within range to copy", Type:=8)
    '<<---

rng.Select

line2:
n = InputBox("type no. of times you want to be repeated minus 1 for e.g if you wnat to be repeated 3 times type 2")
Range(rng.Offset(1, 0), rng.Offset(n, 0)).EntireRow.Insert
Range(rng, rng.End(xlToRight)).Copy
Range(rng, rng.Offset(n, 0)).PasteSpecial

    'this section is not necessary>>
    'Set rng = rng.Offset(n + 1, 0)
    'If rng = "" Then
    'GoTo line1
    'Else
    'GoTo line2
    'End If

line1:
Application.CutCopyMode = False
    'range("a1").Select 'i don't think you need it
MsgBox "macro over"

    'Stop is not neede

Exit Sub
EH:
    MsgBox "Sub interrupted"

End Sub

Thanks

Community
  • 1
  • 1
user1554940
  • 27
  • 1
  • 3

1 Answers1

0

Added a message and a line that select the last copied cell

rng.Offset(n, 0).Select
MsgBox "selected the last row" & rng.Offset(n, 0).Address

Try the below

Sub test2()

    Dim n As Integer, rng As Range

    On Error GoTo EH
    Set rng = Application.InputBox("Select any cell/cells within range to copy", Type:=8)

    rng.Activate

line2:
    n = InputBox("type no. of times you want to be repeated minus 1 for e.g if you wnat to be repeated 3 times type 2")
    Range(rng.Offset(1, 0), rng.Offset(n, 0)).EntireRow.Insert
    Range(rng, rng.End(xlToRight)).Copy
    Range(rng, rng.Offset(n, 0)).PasteSpecial

    rng.Offset(n, 0).Select
    MsgBox "selected the last row" & rng.Offset(n, 0).Address

line1:
    Application.CutCopyMode = False

Exit Sub
EH:
    MsgBox "Sub interrupted"

End Sub