VB Code for Advance Filter hangs up machine

awagdarikar

Board Regular
Joined
Jun 20, 2008
Messages
115
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p>Hi,</o:p>
<o:p></o:p>
<o:p>I have recorded a VB Code to port data from one sheet named Job Tracking Matrix to the other sheet named RGB Daily News. This code ran perfectly till now but it is now resulting in hanging up my PC right now. The code goes like this,</o:p>
<o:p><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:rect id=_x0000_s1026 style="MARGIN-TOP: 5.25pt; Z-INDEX: 1; MARGIN-LEFT: 0px; WIDTH: 405pt; POSITION: absolute; HEIGHT: 477.75pt" filled="f"></v:rect></o:p>
<o:p>
' RGB_DAILY_NEWs Macro
'<o:p></o:p>

' Macro recorded 7/10/2008 by TE300<o:p></o:p>
'<o:p></o:p>
'Clean the Sheet RGB_DAILY_NEWs before entry of data through Advance Filter
Sheets("RGB DAILY NEWs").Select
Range("A10:M10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents<o:p></o:p>

'Apply Advance Filter and port data to this sheet RGB_DAILY_NEWs
Range("A9:M65536").Select
Sheets("Job Tracking").Range("A15:AX65536").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Job Tracking").Range("A8:AX9"), _
CopyToRange:=Range("A9:M65536"), Unique:=False
Range("A1").Select
Range("C10").Select
ActiveWindow.FreezePanes = True
Sheets("RGB DAILY NEWs").Select
'Color the rows in RGB_DAILY_NEWs
Dim Y As Long, Ycol As Integer
For Y = 10 To 500
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">Select</st1:PlaceName> <st1:PlaceName w:st="on">Case</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("M" & Y).Value
Case 5
Ycol = 8
Case 6, 7, 8
Ycol = 39
Case 9
Ycol = 6
Case 10
Ycol = 4
Case Else
Ycol = xlNone
End Select
Range("A" & Y & ":L" & Y).Interior.ColorIndex = Ycol
Next Y<o:p></o:p>

End Sub<o:p></o:p>
<o:p> </o:p>
<o:p></o:p>
<o:p>The PC hangs at step </o:p>
<o:p></o:p>
<o:p>Sheets("Job Tracking").Range("A15:AX65536").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Job Tracking").Range("A8:AX9"), _
CopyToRange:=Range("A9:M65536"), Unique:=False
</o:p>

<o:p> </o:p>
<o:p></o:p><o:p>Can somebody help me out?</o:p>
<o:p></o:p>
<o:p>Thanks in advance,</o:p>
<o:p></o:p>
<o:p>awagdarikar</o:p>
<o:p></o:p>
<o:p>:confused:</o:p></o:p>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
What do you mean by hangs? If you are getting a runtime error tell us what it is. You may want to try changing the CopyToRange to:

CopyToRange:=Range("A9:M9")
 
Upvote 0
Thanks Andrew for your response,

The machine gets hanged means it takes a huge time and the application goes in not responding mode. But it gives answer after around 20-30 minutes. That too when I am working on a workstation class machine (Duel Core, 4GB RAM).

Is it OK to reduce the number of rows in all the ranges? My data is expected to be in 500 rows maximum but fearing failures and based on bad past experience with Advance Filters I did not want to take chance.

Can you please give some tricks/ do's don'ts for Advance Filters?

Thanks,
Awagdarikar
 
Upvote 0
When I tried the first part of your code with about 500 items execution was instantaneous. So I don't know why it takes so long for you. Have you tried setting calculation to manual?
 
Upvote 0
Thanks All Guys,

I reduced the code by replacing 65536 by 500 and also by setting the Calculations method to manual. But still it takes 22 minutes for processing.

Any guess why it is taking so long?

Andrew, Can I send you a private mail so that you can go through it?

Thanks again,

awagdarikar
 
Upvote 0
Dear All,

Thank you very much for attending this issue.

After trying everything I thought I should try deleting the concerned sheet and adding a new one with same content. And it has worked nicely. My code runs very fast on this.

But i do not know why this happened. Any insights to this are welcome. It will help me design my Tracking sheet better,

awagdarikar
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top