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

G

George

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
 
C

Chip Pearson

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]
 
G

George

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 said:
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]




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
Click to expand...
.
Click to expand...
 
D

Don Guillett

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
George said:
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 said:
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]




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
Click to expand...
.
Click to expand...
Click to expand...
 
G

George

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 said:
Sub deletelastcellinL()
Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
George said:
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 said:
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

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


.
Click to expand...
Click to expand...

.
Click to expand...
 
D

Don Guillett

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
George said:
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 said:
Sub deletelastcellinL()
Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
George said:
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

:

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

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


.
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare two different workbook sheet to show the difference in text 0
Linking worksheet and multiple cells 1
Macro problem 2
Macro - issue with positioning results 3
Development combinations with exclusion of columns 0
Development combinations with exclusion of columns 0
Development combinations with exclusion of columns 0
Delete sheets code error requires object 2

Top