How to move to 1st cell in next row after exit a particular cell.

[Wind54Surfer]

Hi all, a newbie question

I need to move to say: A6 after exiting M5, (then to A7 after exiting M6 and
so on...)

Is there a way to do this even if nothing was entered in the M5 cell?

Thanks in advance,
Emilio

 

[Norman Jones]

Hi Emilio,

Try:
'=============>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 13 Then 'Column M
Cells(Target.Row + 1, "A").Select
End If
Application.EnableEvents = True

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Norman Jones]

Hi Emilio,

Perhaps, nearer to your requirement, try:

'=============>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
Cells(Target.Row + 1, "A").Select
Application.EnableEvents = True
End Sub
'<<=============

 

[Norman Jones]

Hi Emilio,

Or, to avoid next row selection if column A is the active column, try:

'=============>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo XIT
Application.EnableEvents = False
If Target.Column > 1 Then
Cells(Target.Row + 1, "A").Select
End If

XIT:
Application.EnableEvents = True
End Sub
'<<=============

 

[Wind54Surfer]

Thanks Norman,

Actually the first answer worked the best.
All I had to do is changed to "14"

Thanks again,
Emilio

Hi Emilio,

Or, to avoid next row selection if column A is the active column, try:

'=============>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo XIT
Application.EnableEvents = False
If Target.Column > 1 Then
Cells(Target.Row + 1, "A").Select
End If

XIT:
Application.EnableEvents = True
End Sub
'<<=============