Hi people,
I am having an annoying VBA problem. I have a spreadsheet that has many Subs on its 52 Mb and uses data from another worksheet that has 48 Mb and a text file that has another 11 Mb.
It looks like this at the point where I am getting an error:
I have a VBA code that works fine on it but is TOO slow:<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts =<SPAN style="color:#00007F">False</SPAN>
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
<SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, n<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
n = 2
Range("A1").EntireRow.Copy Sheets(Nova).Range("A1")
<SPAN style="color:#00007F">For</SPAN> i = Range("A65536").End(xlUp).Row<SPAN style="color:#00007F">To</SPAN> 1<SPAN style="color:#00007F">Step</SPAN> -1
<SPAN style="color:#00007F">If</SPAN> Range("A" & i).Value = "NC"<SPAN style="color:#00007F">Then</SPAN>
Range("A" & i).EntireRow.Copy Sheets(Nova).Range("A" & n)
n = n + 1
Range("C" & i).EntireRow.Delete
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
Sheets(Nova).Activate
Range("A1:F21").Select<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN>
And when I try to modify it to make it faster, using the code below I get an error 1004 message, telling me an error occurred at the copy function from the range class.<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test2()
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts =<SPAN style="color:#00007F">False</SPAN>
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
<SPAN style="color:#00007F">Dim</SPAN> filterRng<SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> filterRng =<SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">With</SPAN> Sheets(aNome)
<SPAN style="color:#00007F">Set</SPAN> filterRng = .Range("A1", .Range("K65536").End(xlUp))
<SPAN style="color:#00007F">With</SPAN> filterRng
.AutoFilter field:=1, Criteria1:="=NC"
.SpecialCells(xlCellTypeVisible).Copy Sheets(Nova).Range("A1")
.SpecialCells(xlCellTypeVisible).Delete
.Cells(1).EntireRow.Insert
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
Sheets(Nova).Range("1:1").Copy .Range("1:1")
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Set</SPAN> filterRng =<SPAN style="color:#00007F">Nothing</SPAN>
Sheets(Nova).Activate
ActiveWorkbook.Names.Add Name:="tblNC", RefersToR1C1:="=NC_nov!R1C1:R21C6"
Range("A1").Select
Sheets(aNome).Activate
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
Application.DisplayAlerts =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
The interesting part is that I get that error AFTER it has copied all the cells.
So, does anyone knows whats going on? May anyone help me out, please?
I am having an annoying VBA problem. I have a spreadsheet that has many Subs on its 52 Mb and uses data from another worksheet that has 48 Mb and a text file that has another 11 Mb.
It looks like this at the point where I am getting an error:
Sheet1.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Type | Producer | Item | Value | Cost | ||
2 | OP | 14642 | 051747S1 | 390 | 281,6733 | ||
3 | OP | 14642 | 051748S1 | 390 | 281,6733 | ||
4 | OP | 14085 | 1863393M93 | 8476,91 | 7099,0329 | ||
5 | OP | 111301 | MIE400MEXE01 | 225 | 229,95 | ||
6 | OP | 111301 | CPE275ADVE01 | 1254 | 1301,006 | ||
7 | OP | 111301 | CPE283ADVE01 | 842,4 | 860,9328 | ||
8 | OP | 111301 | CPE292ADVE01 | 416 | 425,152 | ||
9 | OP | 111301 | CPE299ADVE01 | 607,2 | 620,5584 | ||
10 | OP | 111301 | MII400BE01 | 2071,3 | 2116,8686 | ||
11 | OP | 111301 | MII5300E01 | 730,4 | 746,4688 | ||
12 | OP | 111301 | MOI5300E01 | 172 | 172 | ||
13 | NC | 14129 | 3456,51 | 0 | |||
14 | OP | 64485 | 01107245/000 | 92,94 | 66,12 | ||
15 | OP | 64485 | 01112916/000 | 222 | 131,31 | ||
16 | OP | 64485 | 01112916/000 | 148 | 87,54 | ||
17 | OP | 64485 | 01112916/000 | 148 | 87,54 | ||
18 | NC | 14129 | 01119241/000 | 108,36 | 820,44 | ||
19 | OP | 14827 | 020033P2 | 156,38 | 123,9 | ||
20 | OP | 14827 | 020033P2 | 491,48 | 389,4 | ||
21 | OP | 14827 | 020033P2 | 491,48 | 389,4 | ||
22 | OP | 14827 | 020033P2 | 491,48 | 389,4 | ||
23 | OP | 14827 | 020033P2 | 335,1 | 265,5 | ||
24 | OP | 14827 | 020033P2 | 647,86 | 513,3 | ||
25 | OP | 14827 | 020033P2 | 982,96 | 778,8 | ||
26 | NC | 14827 | 020033P2 | 96,32 | 1982,4 | ||
27 | OP | 64485 | 020041P1 | 120,6 | 73,4865 | ||
28 | OP | 64485 | 020041P1 | 120,6 | 73,4865 | ||
29 | OP | 64485 | 020041P1 | 120,6 | 73,4865 | ||
30 | OP | 64485 | 020041P1 | 225,42 | 146,973 | ||
can_dst |
I have a VBA code that works fine on it but is TOO slow:<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts =<SPAN style="color:#00007F">False</SPAN>
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
<SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, n<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
n = 2
Range("A1").EntireRow.Copy Sheets(Nova).Range("A1")
<SPAN style="color:#00007F">For</SPAN> i = Range("A65536").End(xlUp).Row<SPAN style="color:#00007F">To</SPAN> 1<SPAN style="color:#00007F">Step</SPAN> -1
<SPAN style="color:#00007F">If</SPAN> Range("A" & i).Value = "NC"<SPAN style="color:#00007F">Then</SPAN>
Range("A" & i).EntireRow.Copy Sheets(Nova).Range("A" & n)
n = n + 1
Range("C" & i).EntireRow.Delete
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
Sheets(Nova).Activate
Range("A1:F21").Select<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN>
And when I try to modify it to make it faster, using the code below I get an error 1004 message, telling me an error occurred at the copy function from the range class.<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test2()
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts =<SPAN style="color:#00007F">False</SPAN>
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
<SPAN style="color:#00007F">Dim</SPAN> filterRng<SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> filterRng =<SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">With</SPAN> Sheets(aNome)
<SPAN style="color:#00007F">Set</SPAN> filterRng = .Range("A1", .Range("K65536").End(xlUp))
<SPAN style="color:#00007F">With</SPAN> filterRng
.AutoFilter field:=1, Criteria1:="=NC"
.SpecialCells(xlCellTypeVisible).Copy Sheets(Nova).Range("A1")
.SpecialCells(xlCellTypeVisible).Delete
.Cells(1).EntireRow.Insert
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
Sheets(Nova).Range("1:1").Copy .Range("1:1")
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">Set</SPAN> filterRng =<SPAN style="color:#00007F">Nothing</SPAN>
Sheets(Nova).Activate
ActiveWorkbook.Names.Add Name:="tblNC", RefersToR1C1:="=NC_nov!R1C1:R21C6"
Range("A1").Select
Sheets(aNome).Activate
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
Application.DisplayAlerts =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
The interesting part is that I get that error AFTER it has copied all the cells.
So, does anyone knows whats going on? May anyone help me out, please?