Size and Speed 1

[GoatieEddie]

Hi Guys,

Two questions if I may:

Firstly, I have a file that has a sheet containing 31780 lines of data and 15 columns. On a separate sheet I have a Pivot Table that accesses that data. There are also a couple of VB routines. However, the file saves slowly to 9.8MB which I find kind of excessive! Can someone give me a clue as to why?

Secondly, one of the VBA codes runs through each line and does a worksheet.vlookup function but this takes ages! Given the speed of computers nowadays surely most macros should run like lightning! Again, any clues warmly received.

Cheers,
GE

 

[xlbo]

1: 31780 rows by 15 columns = 476700 items of data in the pivot table - that's a lot for a pivot table
The way they work is that unless you are using an OLAP cube or external data source, a seperate (very hidden) sheet is created that holds all the formulae necessary to create the different functions. This is what will be bulking up the file.

2: It is not the code taking ages, it is the vlookup formulae. In general, use application.screenupdating = false and application.calculation = xlcalculationmanual whilst entering the formulae and then application.screenupdating = true and application.calculation = xlcalculationautomatic afterwards. Without seeing your code, I can't really help any more than that but it isn't necessary to loop to enter formulae - excel is pretty good at interpretting so you can use

Range("B2:B10000&quot.formula = "=vlookup(A2,'Sheet2'!$A$1:$C$10000,3,false)"

instead of looping.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GoatieEddie]

Hi Geoff,

Didn't realise that the pivot table kept a seperate sheet. Thanks for that.

I do turn of the screenupdate and calculation usually. Does using the .formula work quicker than worksheet.function. The nice thing about worksheet.function is it just leaves the value in the cell!

Cheers,
GE

 

[xlbo]

if you are looping and evaluating a vlookup for each row, it's gonna take much longer than necessary - you could enter the formulae and then paste values

with Range("B2:B10000&quot
.formula = "=vlookup(A2,'Sheet2'!$A$1:$C$10000,3,false)"
.calculate
.copy
.pastespecial paste:=xlpastevalues
end with

This is about the quickest method for entering loadsa formulae but leaving values only


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GoatieEddie]

Looks useful! Thanks!

 

[GoatieEddie]

Geoff,

Looking at my pivot table, there sems to be a lot of rubbish in some of the drop downs which doesn't appear in the referenced data range. Could this be from an older version of the data. How can I get rid of it as it is screwing up some of my macros. And can you unhide the extra (xlveryhidden) sheet to see what is in there? I have tried the obvious code of for each sh in sheets but nothing happens.

ta.
GE

 

[xlbo]

Not sure that you can unhide the pivot sheet - I wouldn't wanna touch it anyway. To get rid of old data, run this:

sub ClearOldData()
activesheet.pivottables(1).pivotcache.missingitemslimit = xlmissingitemsnone
end sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GoatieEddie]

Doesn't seem to like that. I get a 438 error Object doesn;t support this property or method. Can;t find missingitemslimit in help to check it out either....?

 

[GoatieEddie]

Found this on the net for pre-Excel 2002

Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next

End Sub

 

[xlbo]

what version of excel are you running ?? I'm on XP

Just had a look on a colleague's machine and there doesn't seem to be that option in '97. You could try pivottables(1).pivotcache.optimizecache = true

but I'm not entirely sure it'll do what the missingitemslimit does - have a look at help before going aheah with that one....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244