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.

--
Regards

Roger Govier


"Gary''s Student" wrote in
message ...
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.
--
Gary's Student
gsnu200707


"Roger Govier" wrote:

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.

--
Regards

Roger Govier


"Gary''s Student" wrote in
message ...
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

--
Gary's Student
gsnu200707


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Gary''s Student" wrote
in
message ...
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.
--
Gary's Student
gsnu200707


"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Gary''s Student"
wrote
in
message
...
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
--
Gary's Student
gsnu200707


"Richo" wrote:

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?