Set movement of cells in dynamic range only

[Vincent88]

I want to set the cells' movement to right after entering data in a dynamic range of cells (Outside the range - move down after press enter)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Select Case Sh.Name
    Case "Agents"
      Exit Sub
    Case Else
  End Select


Dim LastColumn As Long, LastRow As Long

'Set Cursor Direction to Right in Dynamic Range Only
LastRow = sh.Range("A2").End(xlDown).Row
LastColumn = sh.Range("A2").CurrentRegion.Columns.Count

If Target.Column > 4 Or Target.CountLarge > 1 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
 
  Dim rng As Range
  Set rng = Range(Cells(2, 1), Cells(LastRow, LastColumn))

   With rng
  
If Target.Column = 2 And Not (IsEmpty(Target)) Then
  Target.Offset(, 2).Select
    
 Else
     Target.Offset(, 1).Select
 End If
End With

End Sub

but it happens outside the dynamic range also. How should I correct it ?

 

[shknbk2]

Right now, there are too many unknowns based solely on your code.

What are you expecting the dynamic range to be? Does it have anything to do with LastRow/LastColumn?
How many columns are in the CurrentRegion? (Does it extend beyond column D? I ask because you check that Target.Column > 4)
In what cells does the movement to the right happen that you don't want it to?
Where should it happen?

Right now, the code would offset 1 or 2 to the right as long as the Target was anywhere in columns A-D outside of row 1.

 

[Vincent88]

Hi shknbk2,
As the dynamic range will grow when entering data in new row continously to the last filled row. I just want the application to apply to the range only. If the active cell is beyond the range, then the cell movement will be moving down as usual. (For instance, if I enter data in row 9 downwards, the cell movement will be moving down).

 

[shknbk2]

Ok. How about something like this?

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Agents" Or Target.CountLarge > 1 Then Exit Sub
    
    Dim rng As Range
    Set rng = Range("A1").CurrentRegion
    If rng.Rows.Count > 1 Then
        Set rng = Intersect(Target, rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count))
    Else
        Set rng = Nothing
    End If
    If Not rng Is Nothing Then
        If Target.Column = 2 And Not (IsEmpty(Target)) Then
            Target.Offset(, 2).Select
        Else
            Target.Offset(, 1).Select
        End If
    End If
End Sub

 

[Vincent88]

Hi shknbk2,
It is strange that when in D rows, it won't move to E rows after pressing enter.

 

[Vincent88]

Hi shknbk2,
I adjust sequence of my other codes, it works now. Thanks.