Find last cell in a column, Delete its contents and make it active
 

Good Afternoon,

I have two worksheets, one named "Open Issues" and the other "Closed
Issues". In the code I've attached what happens is as soon as a completed
date is added to a cell row in "Open Issues" that row is then copied to the
next open row in the "Completed Issues" sheet and deleted from the "Open
Issues" and "Closed Issues" sheet is made active..

This is where I need help...Since the copied row is at the bottom, I need to
locate the last entry in lets say "Column L", delete its contents and make it
active for the user to input new information..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Closed issues")
lngNRow = ws2.Cells(Rows.Count, "H").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("H" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
ws2.Activate

End If
Next


End Sub


Thanks in advance
George

Find last cell in a column, Delete its contents and make it active
 

Try something like

Dim J As Long
With WS2
J = .Cells(.Rows.Count, "L").End(xlUp).Row
.Rows(J).Delete
Application.Goto .Cells(J, "A")
End With


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Wed, 27 Jan 2010 09:36:15 -0800, George
wrote:

Good Afternoon,

I have two worksheets, one named "Open Issues" and the other "Closed
Issues". In the code I've attached what happens is as soon as a completed
date is added to a cell row in "Open Issues" that row is then copied to the
next open row in the "Completed Issues" sheet and deleted from the "Open
Issues" and "Closed Issues" sheet is made active..

This is where I need help...Since the copied row is at the bottom, I need to
locate the last entry in lets say "Column L", delete its contents and make it
active for the user to input new information..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Closed issues")
lngNRow = ws2.Cells(Rows.Count, "H").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("H" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
ws2.Activate

End If
Next


End Sub


Thanks in advance
George

Find last cell in a column, Delete its contents and make it ac
 

Chip,
Thank You for your help. The routine works, but not quite like I nee it to
do so if I could ask for some additional direction it would be much
appreciated...Your routine finds the last row and deletes it entirely, what I
need is to ONLY delete the contents located in the cell at the end of column
"L"...Any additional help is appreciated.

Thanks
George

"Chip Pearson" wrote:

Try something like

Dim J As Long
With WS2
J = .Cells(.Rows.Count, "L").End(xlUp).Row
.Rows(J).Delete
Application.Goto .Cells(J, "A")
End With


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Wed, 27 Jan 2010 09:36:15 -0800, George
wrote:

Good Afternoon,

I have two worksheets, one named "Open Issues" and the other "Closed
Issues". In the code I've attached what happens is as soon as a completed
date is added to a cell row in "Open Issues" that row is then copied to the
next open row in the "Completed Issues" sheet and deleted from the "Open
Issues" and "Closed Issues" sheet is made active..

This is where I need help...Since the copied row is at the bottom, I need to
locate the last entry in lets say "Column L", delete its contents and make it
active for the user to input new information..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Closed issues")
lngNRow = ws2.Cells(Rows.Count, "H").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("H" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
ws2.Activate

End If
Next


End Sub


Thanks in advance
George


.

Find last cell in a column, Delete its contents and make it ac
 

Sub deletelastcellinL()
Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"George" wrote in message
...
Chip,
Thank You for your help. The routine works, but not quite like I nee it to
do so if I could ask for some additional direction it would be much
appreciated...Your routine finds the last row and deletes it entirely,
what I
need is to ONLY delete the contents located in the cell at the end of
column
"L"...Any additional help is appreciated.

Thanks
George

"Chip Pearson" wrote:

Try something like

Dim J As Long
With WS2
J = .Cells(.Rows.Count, "L").End(xlUp).Row
.Rows(J).Delete
Application.Goto .Cells(J, "A")
End With


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Wed, 27 Jan 2010 09:36:15 -0800, George
wrote:

Good Afternoon,

I have two worksheets, one named "Open Issues" and the other "Closed
Issues". In the code I've attached what happens is as soon as a
completed
date is added to a cell row in "Open Issues" that row is then copied to
the
next open row in the "Completed Issues" sheet and deleted from the
"Open
Issues" and "Closed Issues" sheet is made active..

This is where I need help...Since the copied row is at the bottom, I
need to
locate the last entry in lets say "Column L", delete its contents and
make it
active for the user to input new information..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Closed issues")
lngNRow = ws2.Cells(Rows.Count, "H").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("H" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
ws2.Activate

End If
Next


End Sub


Thanks in advance
George


.

Find last cell in a column, Delete its contents and make it ac
 

Thanks Don for your timely reply it works great. I have another condition
maybe you can help me with...if you look at my code what's happen is as soon
as a completed date is added to the "Open issues" sheet that row is copied to
the "Closed issues" Sheet and deleted from "Open issues"....Sometimes a
"closed issue" needs to be reopened so what I'd like to have happen is if the
completed date is removed the issue get moved back to the "Open Issues"
sheet...Any suggestions would be greatly appreciated.

Thanks In Advance ,
George

"Don Guillett" wrote:

Sub deletelastcellinL()
Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"George" wrote in message
...
Chip,
Thank You for your help. The routine works, but not quite like I nee it to
do so if I could ask for some additional direction it would be much
appreciated...Your routine finds the last row and deletes it entirely,
what I
need is to ONLY delete the contents located in the cell at the end of
column
"L"...Any additional help is appreciated.

Thanks
George

"Chip Pearson" wrote:

Try something like

Dim J As Long
With WS2
J = .Cells(.Rows.Count, "L").End(xlUp).Row
.Rows(J).Delete
Application.Goto .Cells(J, "A")
End With


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Wed, 27 Jan 2010 09:36:15 -0800, George
wrote:

Good Afternoon,

I have two worksheets, one named "Open Issues" and the other "Closed
Issues". In the code I've attached what happens is as soon as a
completed
date is added to a cell row in "Open Issues" that row is then copied to
the
next open row in the "Completed Issues" sheet and deleted from the
"Open
Issues" and "Closed Issues" sheet is made active..

This is where I need help...Since the copied row is at the bottom, I
need to
locate the last entry in lets say "Column L", delete its contents and
make it
active for the user to input new information..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Closed issues")
lngNRow = ws2.Cells(Rows.Count, "H").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("H" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
ws2.Activate

End If
Next


End Sub


Thanks in advance
George


.


.

Find last cell in a column, Delete its contents and make it ac
 

Send the file to me and I'll have a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"George" wrote in message
...
Thanks Don for your timely reply it works great. I have another condition
maybe you can help me with...if you look at my code what's happen is as
soon
as a completed date is added to the "Open issues" sheet that row is copied
to
the "Closed issues" Sheet and deleted from "Open issues"....Sometimes a
"closed issue" needs to be reopened so what I'd like to have happen is if
the
completed date is removed the issue get moved back to the "Open Issues"
sheet...Any suggestions would be greatly appreciated.

Thanks In Advance ,
George

"Don Guillett" wrote:

Sub deletelastcellinL()
Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"George" wrote in message
...
Chip,
Thank You for your help. The routine works, but not quite like I nee it
to
do so if I could ask for some additional direction it would be much
appreciated...Your routine finds the last row and deletes it entirely,
what I
need is to ONLY delete the contents located in the cell at the end of
column
"L"...Any additional help is appreciated.

Thanks
George

"Chip Pearson" wrote:

Try something like

Dim J As Long
With WS2
J = .Cells(.Rows.Count, "L").End(xlUp).Row
.Rows(J).Delete
Application.Goto .Cells(J, "A")
End With


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Wed, 27 Jan 2010 09:36:15 -0800, George
wrote:

Good Afternoon,

I have two worksheets, one named "Open Issues" and the other "Closed
Issues". In the code I've attached what happens is as soon as a
completed
date is added to a cell row in "Open Issues" that row is then copied
to
the
next open row in the "Completed Issues" sheet and deleted from the
"Open
Issues" and "Closed Issues" sheet is made active..

This is where I need help...Since the copied row is at the bottom, I
need to
locate the last entry in lets say "Column L", delete its contents and
make it
active for the user to input new information..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngNRow As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets("Closed issues")
lngNRow = ws2.Cells(Rows.Count, "H").End(xlUp).Row
For lngRow = ws1.Cells(Rows.Count, "H").End(xlUp).Row To 2 Step -1
If IsDate(ws1.Range("H" & lngRow)) Then
lngNRow = lngNRow + 1
ws1.Rows(lngRow).Copy ws2.Rows(lngNRow)
ws1.Rows(lngRow).Delete
ws2.Activate

End If
Next


End Sub


Thanks in advance
George


.


.