Auto return

[Guest]

I want to be able to go automate after entering data in the last cell in a
row to then go back to the beginning of the next line?

 

[Guest]

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Cells.Columns.Count Then
Exit Sub
Else
i = Target.Row + 1
Cells(i, 1).Select
End If
End Sub

REMEMBER worksheet code, not a standard module

 

[Roger Govier]

Hi

Unless the user wants to use the whole row of 256 columns before jumping
to the next line, wouldn't they need to insert a value equal to their
last column number, rather than using
Cells.Columns.Count

If Target.Column <> Cells.Columns.Count Then

Cells.Columns.Count always returns 256 (in XL2003 and lower)

 

[Guest]

But Roger, the OP specifically want the auto-return after data was entered in
the last cell in the row. Usually the last cell in a row is the cell in
column 256 (column IV) I used Cells.Columns.Count in case the OP was using
Excel 2007.

 

[Roger Govier]

Hi

Reading the OP's post literally, you are absolutely right.
I had assumed (perhaps wrongly) that he had data stretching across maybe
a dozen or so columns, and wanted to return to the beginning of the next
line after entering the last of "his" row of data.

Shows how we can all interpret a question quite differently <bg>

 

[Guest]

O.K. then.... to auto-return from the last cell in the currently used range:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
If Target.Column <> nLastColumn Then
Exit Sub
Else
i = Target.Row + 1
Cells(i, 1).Select
End If
End Sub

 

[Roger Govier]

Hi

Firstly, I don't know how to address you, even though I have read so
many of your postings over the years.
Is it Gary?

Secondly, that is excellent and adjusts to the relevant last column
without the user having to specify the column number.
However, could you explain the use of +r.Column - 1
r.column returns 1, hence I don't see why this has been included.

As a totally different alternative for the OP, and the way in which I
usually enable users to quickly enter data into an area of the sheet
with automatic dropping to the next row, is to set Scroll Area. This is
a toggle which switches on or off the scroll area on Sheet1

Sub LockArea()
If Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" Then
Sheets("Sheet1").ScrollArea = ""
Else
Sheets("Sheet1").ScrollArea = "$D$2:$M$1000"
End If
End Sub

This sub has to be entered into a standard module of course, and
activated by selecting the macro (or its shortcut) first.

Whilst one needs to know in advance the area you are locking to, it has
the advantage of not using any processing power during data entry
looking at change events, and even if you do not enter data in the last
column, pressing enter will take you to the beginning of the next row.

 

[Gord Dibben]

Richo

Example ranges only..............

Select A1 then Tab across to J1.

Hit ENTER key to move to A2

Alternative method.

Unlock columns A:J then protect the sheet.


Gord Dibben MS Excel MVP

 

[Guest]

You can certainly call me Gary. My username is dedicated to the person who
taught me Excel. I use it to always remind me to show the same patience to
others as he showed to me.

About
r.Columns.Count + r.Column - 1

Consider a worksheet that is completely empty except for cells K1 and L1
r.Columns.Count returns a 2 - the width of the range
r.Column returns 11 - where the range begins
The sum of these two values is 13 - because we are counting column K twice.
That's why we subtract one - we want the last used column, not the first
column after the last used coumn.

 

[Roger Govier]

Hi Gary

Thank you.
I can see that now.
I had been testing with a range that ran from A to M, so columns.count =
13, and the r.columns.count - 1 wasn't changing the value of
nlastcolumn.

Yet again, my VBA knowledge has been enhanced.