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

Sub uselastcellinL()
Application.Goto Cells(Rows.Count, "L").End(xlUp)
ActiveCell.ClearContents
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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 Again Don,

I added something to my previous post, could you look at it and give me your
suggestions....Thanks George

"Don Guillett" wrote:

Sub uselastcellinL()
Application.Goto Cells(Rows.Count, "L").End(xlUp)
ActiveCell.ClearContents
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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


.



.