Problems executing Excel VBA code in Microsoft Office 2003

[Umesh]

Hi,

I have written a VBA function in a Microsoft Excel 2002
Work Sheet. The function is used to filter rows depending
upon the selections made in 2 combo boxes in the work
sheet. I Hide\Unhide the rows, depending upon the
selections made in the combo boxes. I make row by row
iteration to find if the row has to be hidden or
unhidden. The worksheet typically has about 5000 rows.
The function is written in the Change event of the Combo
Boxes.

When I open this Work Sheet on the machine which has
Excel 2002 installed on it, it takes about 30 seconds to
execute this function. However, when I open the same work
sheet in Excel 2003, it does not complete even after 30
minutes and the Task Manager shows that the Work sheet is
not responding.

I have fine tuned the code for this function to a large
extend and it is well reflected when opened on a machine
with Excel 2002. It takes hardly 30 seconds for filtering
5000 rows. Can anybody help me in knowing some
limitations\features of Excel 2003 which are creating
problems for me?.

Any help would be greatly appreciated.

Thanks
Umesh

 

[Bernie Deitrick]

Umesh,

Instead of iterating through your range, you should simply rely on Excel's native filtering function, along the lines of this, where
F12 and F13 are the linked cells for the two combo boxes:

Range("A1:A5000").AutoFilter _
Field:=1, _
Criteria1:="=" & Range("F12").Value, _
Operator:=xlOr, _
Criteria2:="=" & Range("F13").Value

HTH,
Bernie
Excel MVP

 

[Bernie Deitrick]

Umesh,

Instead of using SUM, use SUBTOTAL. Look in help for the correct syntax,
but subtotal only operates on visible cells, so will return the correct sum
after filtering. You can place the formula after filtering, or simply don't
inlcude the formula's row in the filtered range.

HTH,
Bernie

 

[Umesh]

Hi Bernie,

Thanks for the suggestion. I am now using the Subtotal function
instead of SUM and it seems to work fine.
I have also found out why the execution was taking longer in excel
2003. Excel 2003 evaluates the formulas on the sheet in a different
manner then in excel 2000. Even if we hide/unhide rows, the formulas
on the work sheet are recalculated which was not the case in excel
2000. So I just turned off the formulas while the filtering function
was working. i used Application.Calculation = xlCalculationManual and
once the filtering was over, I reset it to Application.Calculation =
xlCalculationAutomatic.

Thanks for all the help.