VBA Excel memory problem (easy code)

[Mange]

Hi all

I wrote a simple code that gives me surprisingly (probably not surprising for you) a memory problem: "There isn't enough memory to complete this action..".

I want to copy/paste rows from a sheet to another sheet based on two conditions. After it is pasted I want to delete those rows from the original sheet. This work fine.

Afterwards there are a number of rows that I don't want in the second sheet. So I want to delete them. Their name starts with ROM and POL so it is easy to find with the LEFT function. But it doesn't work. There are only 30 rows in the second sheet.

It bugs here: wbCS.Sheets("Test").Rows(myRow).Delete<o></o>

Can anybode see the problem?

Dim wsSpreadAs Worksheet<o:p></o:p>
Dim wsTest AsWorksheet<o:p></o:p>
Dim wbCS AsWorkbook<o:p></o:p>
Dim LastRowS AsLong<o:p></o:p>
Dim LastRowTestAs Long<o:p></o:p>
Dim myRow AsLong<o:p></o:p>
    <o:p></o:p>
Set wbCS =ThisWorkbook<o:p></o:p>
Set wsSpread =wbCreditSpread.Sheets("Spread ")<o:p></o:p>
Set wsTest =wbCreditSpread.Sheets("Test")<o:p></o:p>
    <o:p></o:p>
LastRowS =wbCreditSpread.Sheets("Spread ").Cells(Rows.Count, "BP").End(xlUp).Row<o:p></o:p>
LastRowTest =wbCreditSpread.Sheets(“Test").Cells(Rows.Count,"A").End(xlUp).Row<o:p></o:p>
<o:p></o:p>
For myRow =LastRowSpread To 1 Step -1<o:p></o:p>
    <o:p></o:p>
If wbCS.Sheets("Spread").Cells(myRow, "BP") = "Sovereign" And wbCS.Sheets("Spread").Cells(myRow, "BS") = "Non Euros" Then<o:p></o:p>
        <o:p></o:p>
wbCS.Sheets("Test").Rows(LastRowTest+ 1).Value = wbCS.Sheets("Spread ").Rows(myRow).Value<o:p></o:p>
wbCS.Sheets("Spread").Rows(myRow).Delete<o:p></o:p>
LastRowTest = LastRowTest + 1<o:p></o:p>
        <o:p></o:p>
End If<o:p></o:p>
        <o:p></o:p>
Next myRow<o:p></o:p>
    <o:p></o:p>
For myRow =LastRowTest To 1 Step -1<o:p></o:p>
        <o:p></o:p>
If Left(wbCS.Sheets("Test").Cells(myRow,"L"), 3) = "ROM" Or Left(wbCS.Sheets("Test").Cells(myRow,"L"), 3) = "POL" Then<o:p></o:p>
        <o:p></o:p>
wbCS.Sheets("Test").Rows(myRow).Delete<o:p></o:p>
LastRowTest= LastRowTest + 1<o:p></o:p>
            <o:p></o:p>
End If<o:p></o:p>
                <o:p></o:p>
Next myRow<o:p></o:p>
    <o:p></o:p>
End Sub<o:p></o:p>

 

[Mange]

Follow up question:

When I only use the beginning of the code:

Dim wsSpreadAs Worksheet<o:p></o:p>
Dim wsTest AsWorksheet<o:p></o:p>
Dim wbCS AsWorkbook<o:p></o:p>
Dim LastRowS AsLong<o:p></o:p>
Dim LastRowTestAs Long<o:p></o:p>
Dim myRow AsLong<o:p></o:p>
    <o:p></o:p>
Set wbCS =ThisWorkbook<o:p></o:p>
Set wsSpread =wbCreditSpread.Sheets("Spread ")<o:p></o:p>
Set wsTest =wbCreditSpread.Sheets("Test")<o:p></o:p>
    <o:p></o:p>
LastRowS =wbCreditSpread.Sheets("Spread ").Cells(Rows.Count, "BP").End(xlUp).Row<o:p></o:p>
LastRowTest =wbCreditSpread.Sheets(“Test").Cells(Rows.Count,"A").End(xlUp).Row<o:p></o:p>
<o:p></o:p>
For myRow =LastRowSpread To 1 Step -1<o:p></o:p>
    <o:p></o:p>
If wbCS.Sheets("Spread").Cells(myRow, "BP") = "Sovereign" And wbCS.Sheets("Spread").Cells(myRow, "BS") = "Non Euros" Then<o:p></o:p>
        <o:p></o:p>
wbCS.Sheets("Test").Rows(LastRowTest+ 1).Value = wbCS.Sheets("Spread ").Rows(myRow).Value<o:p></o:p>
wbCS.Sheets("Spread").Rows(myRow).Delete<o:p></o:p>
LastRowTest = LastRowTest + 1<o:p></o:p>
        <o:p></o:p>
End If<o:p></o:p>
        <o:p></o:p>
Next myRow<o:p></o:p>

And I want to delete the concerning lines manually, it gives me also a warning that it will take too long. When I choose to delete, the same warning appears: "There isn't enough memory to complete this action..."

When I want to activate another macro, it gives me the 'Overflow' error.

Anybody that can help?