office2003 slower??

tess457

New Member
Joined
Aug 4, 2004
Messages
34
Does anyone find that Excel2003 runs slower than excel97. My company has just upgraded excel97 to excel2003 and I am finding that one of my macros that delete mulitiple rows is running alot slower than it did in excel97.


Code:
Function Filter(FVar) 
     
     '==============================================================================================================
     '   Local Variables
    Dim rnData As Range 'stores range of used data
    Dim irows As Integer 'stores number of used rows
    Dim icolumns As Integer 'stores column number where it sorts through the True & False. (Always Col F or #6)
     '==============================================================================================================
     
    Application.ScreenUpdating = False 
    Range("A1").Select 
     
    Set rnData = ActiveSheet.UsedRange 'stores range of used data
    Let irows = rnData.CurrentRegion.Rows.Count 'number of used rows
    Let icolumns = 6 
     
    If Range("A2") = Empty Then 
        Goto Finish 
    End If 
     
    Cells(1, icolumns).FormulaR1C1 = "Sort" 'Labels the heading of Col F to Sort
    Cells(2, icolumns).Select 'Selects next row down ("F2")
     
    Selection.FormulaR1C1 = "=OR(RC[-3]={""" & FVar & """})" 'This formula checks to see if ColC "Stations" EQUALS any of
     'the Filter Criteria stored in FVAR. If so, it sets ColF to True,
     'if not, it sets it to false.
     
    Selection.Copy Destination:=Range(Cells(3, icolumns), Cells(irows, icolumns)) 'Copies same formula as above, to all other USED rows.
     
    Set rnData = ActiveSheet.UsedRange 'Stores range of used data in rnData again, just in case there is no more data
     
    Selection.AutoFilter 'Applies autofilter to all the headings.
     
    With rnData 'Filters out all the Data that have ColF equal False.
        .AutoFilter Field:=6, Criteria1:="False" 
        .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _ 
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Deletes entire row of any data that has ColF=False.
        .AutoFilter 'Shuts off autofilter
    End With 
     
    rnData.Columns(icolumns).Delete 'Deletes ColF, gets rid of True/False
     
    Application.ScreenUpdating = True 
     
     
    Range("A2").Select 
Finish: 
    Counter 'Updates counter on status bar, of how many events are left
End Function

The " .entirerow.delete" is the command that is taking all the time. Does anyone have any suggestions on how to get around this?? I would like to just use autofilter, but autofilter only lets me setup two different criteria, I need around 20 different criteria.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,122,381
Messages
5,595,843
Members
414,025
Latest member
haraop

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