Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
selecting next empty cell
How do I automatically select the next empty cell, in column "A", when data
has been inserted. -- ASU |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Blank (empty) cell always equal to 0?? | Excel Discussion (Misc queries) | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
GET.CELL | Excel Worksheet Functions |