CODEWITHSUNDEEP
excelvba
James Chen
James Chen

Reputation: 237

Code running slow and often receives Run out of Resources Error

Every month i get a billing record of roughly 5,000 lines in whats called a flat file. within the 5,000+ lines there are multiple billing types (read billable services). So example. Customer A could have 18 different bill types and customer B could have 25.
Each bill type has 2 lines, a header line and a data line. and each bill type has different headers and the number of columns. Except for the 1st 3 which is always customer number, date, record type(bill type). in addition each bill type will need to have their own worksheet.

So here's what I've done. 

Sub BillType2()

'Clearing the destination worksheet of previous data

Sheets("REC_type_2_summary").Activate
Rows("2:2").Select
Selection.AutoFilter
Range("B3:I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

'back to source file

Sheets("CGT_REPORT (3)").Activate
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter

'setting filter for record type and eliminating the header rows.

ActiveSheet.Range("$A$1:$AL$14637").AutoFilter Field:=3, Criteria1:="2"
ActiveSheet.Range("$A$1:$AL$14637").AutoFilter Field:=4, Criteria1:="<>*Exhibit*"

Range("A2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Pasting result back to the destination sheet for that record type

 Sheets("REC_type_2_summary").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Columns(3).NumberFormat = "mm/dd/yy"
Range("C1").NumberFormat = "###"

If Range("C1") > 0 Then
Rows("2:2").Select
Selection.AutoFilter Field:=10, Criteria1:="<>0"
End If

End Sub

Now I do a similar process with about 35 other bill types, each with just slightly different criterias. then i put them all together in one large macro called runAll which is just basically me calling all 35 macros.

70% of the time it runs fine. however other times it either takes for ever or i'll get the error that shows I'm running out of resources. How to make this more efficient?

Upvotes: 0

Views: 52

Answers (1)

dwirony
dwirony

Reputation: 5450

I took a shot at cleaning this up for you, getting rid of Select and Activate. One more thing that I didn't look into was checking rows to make sure if they're already auto-filtered or not - that could be an issue, if you're not certain of their status.

Note that instead of referencing the worksheets over and over, we just assign them to sht1 and sht2 at the start.

This should run A LOT faster (if set up properly, I did NOT test on my computer).

Sub BillType2()

'Run faster
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Get some lastrow values set up here
Dim sht1 As Worksheet, sht2 As Worksheet, lastrow As Long
Set sht1 = ThisWorkbook.Worksheets("REC_type_2_summary")
Set sht2 = ThisWorkbook.Worksheets("CGT_report (3)")
lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row

'Clearing the destination worksheet of previous data
sht1.Rows("2:2").AutoFilter
sht1.Range("B3:I" & lastrow).ClearContents

'back to source file

lastrow = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row

sht2.Rows("1:1").AutoFilter

'setting filter for record type and eliminating the header rows.

sht2.Range("$A$1:$AL$" & lastrow).AutoFilter Field:=3, Criteria1:="2"
sht2.Range("$A$1:$AL$" & lastrow).AutoFilter Field:=4, Criteria1:="<>*Exhibit*"

lastrow = sht2.Cells(sht.Rows.Count, "A").End(xlUp).Row

sht1.Range("B3:I" & lastrow + 1).Value = _
sht2.Range("A2:H" & lastrow).Value

sht1.Columns(3).NumberFormat = "mm/dd/yy"
sht1.Range("C1").NumberFormat = "###"

If sht1.Range("C1") > 0 Then
    sht1.Rows("2:2").AutoFilter Field:=10, Criteria1:="<>0"
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 3

Related Questions