Excel memory leak?
 

I've written the following code to compare one list to a second, if
the entry in list 1 does not exist in list 2 it is deleted from list
1.

The results are not correct when it is run, some are deleted that
should be and some are missed. I am relatively sure this code is
correct, when I step through it seems to run correctly. Only when the
script is run it seems to miss things.

Is a memory leak causing this problem?

--

Sub Extract()

Dim i As Integer
Dim wbSummary As Worksheet
Dim wbCurrent As Worksheet
Dim number As Integer
Dim surname As String
Dim r As Integer
Dim d As Boolean

Set wbSummary = Worksheets("Summary")


Application.ScreenUpdating = False

i = 4
For i = 4 To 2479

number = wbSummary.Cells(i, 4)
surname = UCase(wbSummary.Cells(i, 2))
Select Case wbSummary.Cells(i, 5)
Case Is = "A"
Set wbCurrent = Worksheets("TT")
Case Is = "B"
Set wbCurrent = Worksheets("TT")
Case Is = "C"
Set wbCurrent = Worksheets("DD")
Case Is = "D"
Set wbCurrent = Worksheets("DD")
Case Is = "E"
Set wbCurrent = Worksheets("AA")
Case Is = "F"
Set wbCurrent = Worksheets("AA")
End Select

r = 4
For r = 4 To 3441
If wbCurrent.Cells(r, 6).Value = number Then
If UCase(wbCurrent.Cells(r, 4)) = surname Then
d = True
Exit For
Else
d = False
End If
Else
d = False

End If
Next
If d = False Then
wbSummary.Rows(i).Delete
Else

End If

Next

End Sub

Excel memory leak?
 

Try going backwards through the loops

When looping forwards adjacent rows that meet the criteria wont be deleted.

Mike

"airwot4" wrote:

I've written the following code to compare one list to a second, if
the entry in list 1 does not exist in list 2 it is deleted from list
1.

The results are not correct when it is run, some are deleted that
should be and some are missed. I am relatively sure this code is
correct, when I step through it seems to run correctly. Only when the
script is run it seems to miss things.

Is a memory leak causing this problem?

--

Sub Extract()

Dim i As Integer
Dim wbSummary As Worksheet
Dim wbCurrent As Worksheet
Dim number As Integer
Dim surname As String
Dim r As Integer
Dim d As Boolean

Set wbSummary = Worksheets("Summary")


Application.ScreenUpdating = False

i = 4
For i = 4 To 2479

number = wbSummary.Cells(i, 4)
surname = UCase(wbSummary.Cells(i, 2))
Select Case wbSummary.Cells(i, 5)
Case Is = "A"
Set wbCurrent = Worksheets("TT")
Case Is = "B"
Set wbCurrent = Worksheets("TT")
Case Is = "C"
Set wbCurrent = Worksheets("DD")
Case Is = "D"
Set wbCurrent = Worksheets("DD")
Case Is = "E"
Set wbCurrent = Worksheets("AA")
Case Is = "F"
Set wbCurrent = Worksheets("AA")
End Select

r = 4
For r = 4 To 3441
If wbCurrent.Cells(r, 6).Value = number Then
If UCase(wbCurrent.Cells(r, 4)) = surname Then
d = True
Exit For
Else
d = False
End If
Else
d = False

End If
Next
If d = False Then
wbSummary.Rows(i).Delete
Else

End If

Next

End Sub

Excel memory leak?
 

With only a quick glance I don't see anything obvious in your code to cause
memory leak.

I see you are deleting rows(i) in a loop, might be an idea to loop like this

For i = 2479 To 4 Step -1

In passing suggest change all your 'As Integer' declarations to 'As Long'.
Slightly more efficient (perhaps not noticeable) and will cater for any
future possibility of your code needing to handle row numbers over 32k

Regards,
Peter T


"airwot4" wrote in message
oups.com...
I've written the following code to compare one list to a second, if
the entry in list 1 does not exist in list 2 it is deleted from list
1.

The results are not correct when it is run, some are deleted that
should be and some are missed. I am relatively sure this code is
correct, when I step through it seems to run correctly. Only when the
script is run it seems to miss things.

Is a memory leak causing this problem?

--

Sub Extract()

Dim i As Integer
Dim wbSummary As Worksheet
Dim wbCurrent As Worksheet
Dim number As Integer
Dim surname As String
Dim r As Integer
Dim d As Boolean

Set wbSummary = Worksheets("Summary")


Application.ScreenUpdating = False

i = 4
For i = 4 To 2479

number = wbSummary.Cells(i, 4)
surname = UCase(wbSummary.Cells(i, 2))
Select Case wbSummary.Cells(i, 5)
Case Is = "A"
Set wbCurrent = Worksheets("TT")
Case Is = "B"
Set wbCurrent = Worksheets("TT")
Case Is = "C"
Set wbCurrent = Worksheets("DD")
Case Is = "D"
Set wbCurrent = Worksheets("DD")
Case Is = "E"
Set wbCurrent = Worksheets("AA")
Case Is = "F"
Set wbCurrent = Worksheets("AA")
End Select

r = 4
For r = 4 To 3441
If wbCurrent.Cells(r, 6).Value = number Then
If UCase(wbCurrent.Cells(r, 4)) = surname Then
d = True
Exit For
Else
d = False
End If
Else
d = False

End If
Next
If d = False Then
wbSummary.Rows(i).Delete
Else

End If

Next

End Sub

Excel memory leak?
 

Cheers, that worked a treat (though I'm yet to sanity check it looks
right to me).

I don't really understand why looping backwards helped.

Excel memory leak?
 

I can show you a quick example as to why, got me for a while!
if you have data in your columns such as
1 A
2 A
3 B
4 B
and you want to delete the A's, on a for i=1 to 4 the A in row 1 is deleted,
this puts the second a in row 1 like this
1 A
2 B
3 B
4
Then the next increments i to 2 so it checks the B in row 2 and skips the A
that is in row 1. This doesn't happen when looping backward because you have
already checked any data that will take it place, as in the example above you
have checked the B's in rows 3 and 4, when you delete row 2 it is replaced by
a B that you already checked so it is safe to loop. Hope that makes sense.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"airwot4" wrote:

Cheers, that worked a treat (though I'm yet to sanity check it looks
right to me).

I don't really understand why looping backwards helped.

Excel memory leak?
 

Say in your forward loop between 1-10 you will want to delete just Rows 4 &
5.

After deleting Row-4 what was Row-5 is now Row-4 and this 'old' row-5 won't
get deleted. Old row-6 is now row-5 and that will be deleted.

row-4 correctly deleted
row-5 wrongly not deleted, now exists as row-4
row-6 incorrectly deleted

Of course it would be possible to track rows as they are deleted and adjust
the index accordingly. However, with looping and deleting from bottom up, a
smaller total qty of rows will be incremented upwards and the code will run
faster.

Regards,
Peter T

"airwot4" wrote in message
ups.com...
Cheers, that worked a treat (though I'm yet to sanity check it looks
right to me).

I don't really understand why looping backwards helped.

Excel memory leak?
 

That's great; thank you very much.