Moving an entire row based on a cell having a date in it.

Hi everyone,

I have this VBA code that I found and it works but only if the cell is filled in with "Done". However I would like to have it move the row of cells when there is a returned date put in. The other thing is also that the dates will always be different. So is there a way to move it with the cell just having anything in it? Also is there a way to have this be done automatically once data is in the column G location?

Sub Cheezy()
'Updated by Kutools for Excel 2017/8/28
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Current borrows").UsedRange.Rows.Count
J = Worksheets("Completed borrows").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed borrows").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Current borrows").Range("G1:G" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Done" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Completed borrows").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Done" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub

That works perfectly. Thank you very much.

Here is another option, although not as efficient as @Mumps1

In the workbook sheet module list the following:

Please Login or Register  to view this content.

Mumps,

Elegant code, but should the 'set' statements not be reset to Nothing at the end of the Macro?

I had it drummed into me back in the Dark Ages that if you really need a variable, set it to Nothing once you don't need it anymore, because Excel doesn't always release the 'addressed' memory when the lifespan of that variable ends, especially if it is 'scoped' for the module, and even more so if it's scoped globally.

Value your insight

Ochimus

Mumps,

I accept this could be a "how many angels can stand on the head of a pin" concern, especially for newcomers, and I appreciate the links you provided, but the MS document itself actually points out that 'Module-level' variables consume memory resources until you reset their values?

Which seems to support what I had hammered into me when I started using Excel.

Of course, not doing it gives you an excellent "cover story" when a Code takes longer than a carrier pigeon to do something simple, as we've all experienced at some point. . .

Ochimus