Advanced filter running *really* slow

[Gadget]

I have an advanced filter that is used to generate a list of only those companies that have data against them - it then uses this list in a drop down to appy another filter and only show the selected ones.
[vba] 'Create the list
Range("FullSheetCompany").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("FilterCompany"), _
CopyToRange:=Range("Z3"), _
Unique:=True[/vba]

It works but I was trying to tidy other stuff and make things pretty; so decided that the scroll bars to the right went on for miles with a tiney bar that jumped 1000 records with a single pixel drag (exaggerating) so I hid rows from 3000 to the end. Now the above bit of code takes 6 mins to actually do anything!

I un-hid the rows and we are back to a second or two. Neither of the ranges reference a hidden rows, row 3 is above the main list on the same sheet, ...

please tell me I don't have to put in my own scrollbar component and code it

(BTW: MSExcell 2003)

[rory]

What are the addresses of the FullSheetCompany and FilterCompany ranges?

[Gadget]

FullSheetCompany: $D$20:$N$1584
FilterCompany: $D$10:$N$11

(Column D contains the company name, column N is a T/F value based on whether there is relevant information in the grid, the ones inbetween contain general stuff like descriptions and part numbers.)

[rory]

If you press Ctrl+End do you go to a cell miles past the end of your actual data? If so, try running this and see if the scrollbars shrink a bit:
[VBA]Sub ResetUsedRange(Optional wks As Worksheet)
Dim lngLastRow As Long, lngLastCol As Long, lngRealLastRow As Long, lngRealLastCol As Long
On Error Resume Next
lngLastRow = 1
lngLastCol = 1
If wks Is Nothing Then Set wks = ActiveSheet
With wks
With .Range("A1").SpecialCells(xlCellTypeLastCell)
lngLastRow = .Row
lngLastCol = .Column
End With
lngRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lngRealLastCol = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
If lngRealLastRow < lngLastRow Then .Range(.Cells(lngRealLastRow + 1, 1), .Cells(lngLastRow, 1)).EntireRow.Delete
If lngRealLastCol < lngLastCol Then .Range(.Cells(1, lngRealLastCol + 1), .Cells(1, lngLastCol)).EntireColumn.Delete
Debug.Print .UsedRange.Count
End With
End Sub
[/VBA]

[Gadget]

nope - sorry.

I thought there may be something hidden somewhere in a distant cell as well so I went to the last cell with info in it, did <ctrl+shift+End> and Edit|Clear|All
Ran the code again, moved the cursor to A1, saved and opened the spreadsheet,...

[rory]

any chance you could post it?

[Paul_Hossler]

Try turning off calcuation (Application.Calculation = xlManual) before filtering and turn it back on after you have the data filtered and see if that helps.

My experience is that filtering sometimes acts like it re-calcuates once for each row filtered.


Paul


Paul

[unmarkedhelicopter]

But surely that would have happened before he put the scroll bars in ?
It's worth a try at the very least though !!!

[Gadget]

{Sorry - just back from Christmas Vacation...}

Try turning off calcuation (Application.Calculation = xlManual) before filtering and turn it back on after you have the data filtered and see if that helps.

My experience is that filtering sometimes acts like it re-calcuates once for each row filtered.

There's nothing really to calculate (except price * quantity) - it's basically just a list of stuff, but I'll put the code in and see if it gets any quicker...

Attached is the spreadsheet in question (Stripped to about 100 items and Zipped to fit under the restrictions); it's not locked or anthing - I just want to see what I can do about these scroll bars

Thanks for your help so far

~G~

[Bob Phillips]

You have conditional fotmatting in all of column IV, even the rows past your data, which seems to be the problem.

Clear that then save it and close and re-open, see if its then ok.

[Gadget]

You have conditional fotmatting in all of column IV, even the rows past your data, which seems to be the problem.

Clear that then save it and close and re-open, see if its then ok.

well spotted - missed that... but tried deleting and saving then clearing and saving (and closing then coming back in again): neither of which worked.

... and just tried putting a conditional format on all the cells in a blank sheet - scroll bars don't extend to the limits. Could be something to do with this, but I doubt it.

[Bob Phillips]

When I ran this code against it, Ctrl-Shift-End just took me to row 145

[vba]

Sub DeleteUnused()

Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

End Sub
[/vba]

[Gadget]

When I ran this code against it, Ctrl-Shift-End just took me to row 145

Am I doing something wrong? I dumped a command button on the page, double clicked it, coppied the code, pasted into the new "on click" event (taking out the duplicate 'sub' commands) and then used the button.

I got an error about filtering the first time (I presume because it was refreshing the calculations), but I have a boolean "running" variable to tell it not to try filtering while it's already in a filter. It seemed to do something (at least it hour-glassed for a short time), but saving, closing and opening then hitting <ctrl+end> still takes me to IU65536 (which is why I think I missed column IV BTW)

I tried it with the file I posted here and the 'master' - same thing. Could it be that this is Excel 2003 rather than the newest one?

[Bob Phillips]

I am using Excel 2003.

It took me to IP one time then IT, but after that code it was only row 145.

[Gadget]

I am using Excel 2003.

It took me to IP one time then IT, but after that code it was only row 145.

I must be doing something different from you... I'm going to try it on another machine and see if it's anything to do with this specific install/computer.

Didn't you get an error message about filtering?

[Gadget]

<dup>

[Bob Phillips]

I did get that message but I saw the Boolean as well.

[Gadget]

Aaaaaaarg.... this is really bugging me now. Tried on another machine; there is nothing on any of the rows beyond 135, but it still won't re-set the scrollbars for me!

(I'm playing with the attached "export" version so that it's the same as you are seeing)

{I've given up on hiding the scrollbars because I still have the same problem, but in reverse - huge scroller button with tiney space above and below; pixel drag moves hundreds of records at a time.}