Set movement of cells in dynamic range only

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
271
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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)
VBA Code:
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 ?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 5, 2021
Messages
271
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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).
dynamic range.png
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Ok. How about something like this?
VBA Code:
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
 
Solution

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
271
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi shknbk2,
It is strange that when in D rows, it won't move to E rows after pressing enter.
 

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
271
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi shknbk2,
I adjust sequence of my other codes, it works now. Thanks.
 

Forum statistics

Threads
1,141,075
Messages
5,704,162
Members
421,331
Latest member
imdumb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top