R
Richo
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?
row to then go back to the beginning of the next line?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
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
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
R
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
Cells.Columns.Count always returns 256 (in XL2003 and lower)
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)
G
Gary''s Student
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.
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.
R
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>
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>
G
Gary''s Student
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
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
R
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.
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.
G
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
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
G
Gary''s Student
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.
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.
R
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.
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.