Extracting entire row to new sheet based on criteria, and then deleting empty row

[phase321]

Dear All,

I am a new user to this forum (and also new to using visual basics), but did recently find a partial solution to my issue in the following link: http://www.excelforum.com/excel-form...-criteria.html

Here is my question.... I am setting up an excel file to use as a task list. I've got it to a point where I have an IF statement and conditional formatting set up so that when you place an "x" in column D it changes the font in the entire row to strikethrough the text. I then have a macro set to a button on my toolbar to clear all "x"-ed rows and move them from the worksheet labeled OPEN to another worksheet in the same workbook labeled CLOSED. The macro works; however I would like to add code to delete the empty rows in the OPEN worksheet after they are moved, and I'm not sure how to do that.

The code I am currently using is as follows (modified just a bit from the one found in the link to another thread that I provided above). Can anyone help me? Thank you!

Sub copyPaste()
Dim ws As Worksheet
Dim wt As Worksheet
Set ws = Sheets("Open")
Set wt = Sheets("Closed")
Dim i As Long
Dim lr As Long
lr = ws.Range("i" & Rows.Count).End(xlUp).Row
Dim lt As Long

For i = 1 To lr
lt = wt.Range("A" & Rows.Count).End(xlUp).Row
If ws.Range("i" & i) = "Closed" Then
ws.Range("i" & i).EntireRow.Cut wt.Range("A" & lt + 1)
End If
Next i
End Sub

PS - - I am only at work for another 10 minutes, so I will answer any return posts tomorrow morning. Thanks again to anyone that can help!!

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[phase321]

Thanks for the quick reply John! I will check it out right away tomorrow morning. Have a good evening!

[Mumps1]

Another approach. Place this macro in the worksheet code module. Right click the tab for the worksheet "Open" and click 'View Code'. Copy and paste the macro into the empty code window that opens up.. Close the code window to return to your sheet. As soon as you enter "Closed" in column I and exit the cell, the corresponding row will automatically be copied sheet "Closed" and deleted from sheet "Open".

Please Login or Register  to view this content.