Row Range Selection From ActiveCell

**werko** · 01-17-2018, 02:03 AM

Hi,
Need help!
To solve this macro, it's giving me an error when I try to select a range in active row with activecell
the purpose of this macro it's move down a row range in sheet2 when right click in cell B2 event is triggered on sheet1.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("B2")) Is Nothing Then
Else
With Worksheets("Sheet2")
    .Activate
    .Range(Target.Address).Offset(-1, 0).Select
Dim lastRow As Long
lastRow = ActiveCell.End(xlRight).Row
Range(ActiveCell, ActiveCell.Offset(lastRow, 0)).Select
ActiveCell.Offset(2, 0).Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
End With
End If
End Sub

Thanks, in advance...

**AlphaFrog** · 01-17-2018, 02:23 AM

I don't know if this produces the result you want, but it wont error.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        With Worksheets("Sheet2")
            .Activate
            .Range(Target.Address).Offset(-1, 0).Select
            Dim lastRow As Long
            lastRow = ActiveCell.End(xlToRight).Row
            .Range(ActiveCell, ActiveCell.Offset(lastRow, 0)).Select
            ActiveCell.Offset(2, 0).Insert Shift:=xlDown
            ActiveCell.Offset(1, 0).Select
        End With
    End If
End Sub