selecting next empty cell
 

How do I automatically select the next empty cell, in column "A", when data
has been inserted.
--
ASU

selecting next empty cell
 

If you inserted the data, press Enter. If it is done by a macro (the data
insertion), then you do not need to select the next empty cell to work with
it. You can use Offset(1, 0) to work with the next cell though

"ASU" wrote:

How do I automatically select the next empty cell, in column "A", when data
has been inserted.
--
ASU

selecting next empty cell
 

toolsoptionseditmove

--
Don Guillett
SalesAid Software

"ASU" wrote in message
...
How do I automatically select the next empty cell, in column "A", when
data
has been inserted.
--
ASU

selecting next empty cell
 

Thank you both very much for getting back so quick. The things is I'm using
the code below to copy and paste formulas to the next row. This I do manually
by selecting the next empty cell in column "A". What I'm trying to do is for
the code to activate when ever data is entered in the last row, so that the
formulas are pasted to the next empty row. I hope that wasn't too borring!!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long


If Target.Column < 1 Then Exit Sub
If Target.Row < 2 Then Exit Sub
If Not IsEmpty(Target) Then Exit Sub 'cell must be empty
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If Target.Row < lastrow + 1 Then Exit Sub 'Row must be empty

Rows(Target.Row - 1).Copy
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
Target.EntireRow.SpecialCells(xlConstants).ClearCo ntents
Application.EnableEvents = False 'should be part of change macro
Target.Select
Application.EnableEvents = True 'should be part of change macro



End Sub

--
ASU


"kassie" wrote:

If you inserted the data, press Enter. If it is done by a macro (the data
insertion), then you do not need to select the next empty cell to work with
it. You can use Offset(1, 0) to work with the next cell though

"ASU" wrote:

How do I automatically select the next empty cell, in column "A", when data
has been inserted.
--
ASU

selecting next empty cell
 

I am trying to understand what you want to do. Let's see if I have it right:

You want to copy data from the preceding row (Columns A through ?) to the
next empty row and you are using column A as your control column because it
will not have blank cells between the starting row and the last row. Do I
have the picture correct?

"ASU" wrote:

Thank you both very much for getting back so quick. The things is I'm using
the code below to copy and paste formulas to the next row. This I do manually
by selecting the next empty cell in column "A". What I'm trying to do is for
the code to activate when ever data is entered in the last row, so that the
formulas are pasted to the next empty row. I hope that wasn't too borring!!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long


If Target.Column < 1 Then Exit Sub
If Target.Row < 2 Then Exit Sub
If Not IsEmpty(Target) Then Exit Sub 'cell must be empty
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If Target.Row < lastrow + 1 Then Exit Sub 'Row must be empty

Rows(Target.Row - 1).Copy
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
Target.EntireRow.SpecialCells(xlConstants).ClearCo ntents
Application.EnableEvents = False 'should be part of change macro
Target.Select
Application.EnableEvents = True 'should be part of change macro



End Sub

--
ASU


"kassie" wrote:

If you inserted the data, press Enter. If it is done by a macro (the data
insertion), then you do not need to select the next empty cell to work with
it. You can use Offset(1, 0) to work with the next cell though

"ASU" wrote:

How do I automatically select the next empty cell, in column "A", when data
has been inserted.
--
ASU

selecting next empty cell
 

This copies formulas in the new blank row when you edit the value in column
1of the currently last row. (Which is what I understood you to say).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
If Target.Column < 1 Then Exit Sub
If Target.Row < 2 Then Exit Sub
If IsEmpty(Target) Then Exit Sub 'cell must not be empty
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If Target.Row < lastrow Then Exit Sub ' Entry must be on last row
If not isempty(Target.offset(1,0)) then Exit sub ' next row must be empty
Rows(Target.Row - 1).Copy Target.Offset(1,0).EntireRow
Application.CutCopyMode = False
On Error Resume Next
Application.EnableEvents = False 'should be part of change macro
Target.offset(1,0).EntireRow.SpecialCells(xlConsta nts).ClearContents
Application.EnableEvents = True 'should be part of change macro
End Sub

--
Regards,
Tom Ogilvy


"ASU" wrote in message
...
Thank you both very much for getting back so quick. The things is I'm
using
the code below to copy and paste formulas to the next row. This I do
manually
by selecting the next empty cell in column "A". What I'm trying to do is
for
the code to activate when ever data is entered in the last row, so that
the
formulas are pasted to the next empty row. I hope that wasn't too
borring!!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long


If Target.Column < 1 Then Exit Sub
If Target.Row < 2 Then Exit Sub
If Not IsEmpty(Target) Then Exit Sub 'cell must be empty
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If Target.Row < lastrow + 1 Then Exit Sub 'Row must be empty

Rows(Target.Row - 1).Copy
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
Target.EntireRow.SpecialCells(xlConstants).ClearCo ntents
Application.EnableEvents = False 'should be part of change macro
Target.Select
Application.EnableEvents = True 'should be part of change macro



End Sub

--
ASU


"kassie" wrote:

If you inserted the data, press Enter. If it is done by a macro (the
data
insertion), then you do not need to select the next empty cell to work
with
it. You can use Offset(1, 0) to work with the next cell though

"ASU" wrote:

How do I automatically select the next empty cell, in column "A", when
data
has been inserted.
--
ASU

selecting next empty cell
 

Thanks very much Tom. But when the code runs to the end, it laeves the
slected cell two rows up. How can I have it to end with the next empty cell
selected.
--
ASU


"Don Guillett" wrote:

toolsoptionseditmove

--
Don Guillett
SalesAid Software

"ASU" wrote in message
...
How do I automatically select the next empty cell, in column "A", when
data
has been inserted.
--
ASU

selecting next empty cell
 

Basically what Im after is to run that code automatically so that the row is
pasted without having to manually select the next cell?
--
ASU


"ASU" wrote:

Thanks very much Tom. But when the code runs to the end, it laeves the
slected cell two rows up. How can I have it to end with the next empty cell
selected.
--
ASU


"Don Guillett" wrote:

toolsoptionseditmove

--
Don Guillett
SalesAid Software

"ASU" wrote in message
...
How do I automatically select the next empty cell, in column "A", when
data
has been inserted.
--
ASU

selecting next empty cell
 

You said you wanted to run the code when you edited the last row - and the
sample code restriced that action to column 1. So the code I posted only
runs when you hit enter after editing the cell in column 1 of the last row.
Then it copies formulas to the cells below that. Where the selection ends
up depends on your move after return setting or where you click with the
mouse. The code does not alter the behavior that would have occured if the
code did not run.

If this doesn't do what you want, then perhaps a very clear explanation of
exactly what you want to happen based on the users action specifically in
what location would provide a better response. For example, the code you
posted fires when the user selects a cell, but you asked for something to
happen when a cell is changed. This would say to change the selectionchange
event to the change event. A so forth based on what you described.

--
Regards,
Tom Ogilvy

"ASU" wrote in message
...
Basically what Im after is to run that code automatically so that the row
is
pasted without having to manually select the next cell?
--
ASU


"ASU" wrote:

Thanks very much Tom. But when the code runs to the end, it laeves the
slected cell two rows up. How can I have it to end with the next empty
cell
selected.
--
ASU


"Don Guillett" wrote:

toolsoptionseditmove

--
Don Guillett
SalesAid Software

"ASU" wrote in message
...
How do I automatically select the next empty cell, in column "A",
when
data
has been inserted.
--
ASU

selecting next empty cell
 

Sorry Tom, your right . I was getting confused. It's working great now.
Many thanks
--
ASU


"Tom Ogilvy" wrote:

You said you wanted to run the code when you edited the last row - and the
sample code restriced that action to column 1. So the code I posted only
runs when you hit enter after editing the cell in column 1 of the last row.
Then it copies formulas to the cells below that. Where the selection ends
up depends on your move after return setting or where you click with the
mouse. The code does not alter the behavior that would have occured if the
code did not run.

If this doesn't do what you want, then perhaps a very clear explanation of
exactly what you want to happen based on the users action specifically in
what location would provide a better response. For example, the code you
posted fires when the user selects a cell, but you asked for something to
happen when a cell is changed. This would say to change the selectionchange
event to the change event. A so forth based on what you described.

--
Regards,
Tom Ogilvy

"ASU" wrote in message
...
Basically what Im after is to run that code automatically so that the row
is
pasted without having to manually select the next cell?
--
ASU


"ASU" wrote:

Thanks very much Tom. But when the code runs to the end, it laeves the
slected cell two rows up. How can I have it to end with the next empty
cell
selected.
--
ASU


"Don Guillett" wrote:

toolsoptionseditmove

--
Don Guillett
SalesAid Software

"ASU" wrote in message
...
How do I automatically select the next empty cell, in column "A",
when
data
has been inserted.
--
ASU