Run-Time 7 Out of Memory issue

[bepedicino]

I am having a small piece of code that is causing my VBA macro to have a Run-Time 7 Out of Memory issue. I am hoping someone can assist.

The error is cause when I get to this portion of the code.

With ActiveSheet
 .Columns.Hidden = False
 .Rows.Hidden = False
 .UsedRange.Value = .UsedRange.Value
End With

Here is the complete VBA macro

Sub ReadyForUpload()
     Application.ScreenUpdating = False

     Dim cell As Range
     For Each cell In Range("A:B, E:E")
         If Len(cell) > 0 Then cell = UCase(cell)
     Next cell
     Application.ScreenUpdating = True
   Const MyTarget = "#N/A" ' <-- change to suit

   Dim Rng As Range, DelCol As New Collection, x
   Dim i As Long, j As Long, k As Long

   ' Calc last row number
  j = Cells.SpecialCells(xlCellTypeLastCell).Row  'can be: j = Range("C" & Rows.Count).End(xlUp).Row

   ' Collect rows range with MyTarget
  For i = 1 To j
     If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
       k = k + 1
       If k = 1 Then
         Set Rng = Rows(i)
       Else
         Set Rng = Union(Rng, Rows(i))
         If k >= 100 Then
           DelCol.Add Rng
           k = 0
         End If
       End If
     End If
   Next
   If k > 0 Then DelCol.Add Rng

   ' Turn off screen updating and events
  Application.ScreenUpdating = False
   Application.EnableEvents = False

   ' Delete rows with MyTarget
  For Each x In DelCol
     x.Delete
   Next

   ' Update UsedRange
  With ActiveSheet.UsedRange: End With

   ' Restore screen updating and events
  Application.ScreenUpdating = True
   Application.EnableEvents = True


With Application
 .Calculate
 .ScreenUpdating = False
 .Calculation = xlCalculationManual
 .DisplayAlerts = False
End With

With ActiveSheet
 .Columns.Hidden = False
 .Rows.Hidden = False
 .UsedRange.Value = .UsedRange.Value
End With

For Each Worksheet In ThisWorkbook.Worksheets
If Worksheet.Name = ActiveSheet.Name Then
Else
 Worksheet.Delete
End If
Next Worksheet

With Application
 .ScreenUpdating = True
 .Calculation = xlCalculationAutomatic
 .DisplayAlerts = True
End With

 Columns("U").NumberFormat = "@"

 Range("A:E").Replace Chr(10), ""

 Range("A:E").Replace Chr(13), ""

 Columns("F").Delete

 Columns("I").Delete

     Const Ffold As String = "\\WS0113\WLDepts$\Administration\Trade Compliance\IT\Integration Point\Daily - Product Classification Upload\"  'change as required
    Dim Fname As String
     Fname = "Product Classification Upload"
     Fname = Fname & " - " & Format(Date, "yyyymmdd") & ".xlsx"
     Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs _
         Filename:=Ffold & Application.PathSeparator & Fname, _
         FileFormat:=xlOpenXMLWorkbook
     Application.DisplayAlerts = True

End Sub

 

[Joe4]

Loops are notoriously slow. So lines like the following could be problematic:

For Each cell In Range("A:B, E:E")

Assuming you are using something newer than Excel 2003, this would loop through over 3 million cells!
Are you really using all 1 million + rows in Excel? If not, it is best to find that last row you need to do loop though, and write your loop to only go that far (and avoid all the unnecessary looping).

Also, I have not done much work with "Collections", but if they get too much, I suspect that they may also be problematic.

 

[bepedicino]

Joe4,

I can't wait to see if that is the fix. As you mentioned I do not want to look at everything, just the first 1001 rows in my Excel 2010 sheet. Since I am not well versed in VBA could you please advise how I should re-write that line?

Thank you!

 

[Joe4]

Try:

    For Each cell In Range("A1:B1001, E1:E1001")

 

[bepedicino]

Thank you for the information, however it did not solve the VBA memory issue that I am having with the script. I have tried the script on multiple machines with varying specification and the issue is always present, therefore I know the issue lies within the VBA, I just don't know how to correct it.

 

[Joe4]

One thing you may try to do is the following. Comment out all of your code. Then little-by-little, un-comments sections (beginning at the top), and try running them. And see if you can locate exactly where/when it is that you get that error. That may be the problem point that we want to focus on.

Also, if you explain (in plain English) exactly what it is this code is supposed to do, we may be able to offer solutions that work better for you.