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>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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")
 

awagdarikar

Board Regular
Joined
Jun 20, 2008
Messages
115
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?
 

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429

ADVERTISEMENT

it's probably because of the 65536...try reduce that...
 

andrewman

Board Regular
Joined
Jul 16, 2008
Messages
206
I suggest you should use the Microsoft query filter the data first.

Andrew Man
 

awagdarikar

Board Regular
Joined
Jun 20, 2008
Messages
115

ADVERTISEMENT

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
 

awagdarikar

Board Regular
Joined
Jun 20, 2008
Messages
115
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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
Top