bestand te groot (ctrl-end methode werkt niet)

[gammelkeunited]

hallo mensen,

ik heb een excel bestand van 24mb
hierdoor duurt het ongeveer 10 min voor het bestand is opgestart en duurt het opslaan ook erg lang.
ik heb geprobeerd het formaat te verkleinen via ctrl end. laatste gebruikte cel. maar dit werkt niet.
ook de bestandscompressie file elders op dit forum. werkt niet.

het bestand bestaat 85 sheets met daarin op elke pagina ongeveer 40 VERT. ZOEKEN- functies, en een 2-tal ALS- functies.
in 40 sheets staat een command button met daarchter het volgende vba programma.

Private Sub CommandButton1_Click()
Dim lRij As Long
Dim lzRij As Long
lRij = 5
lzRij = 1
Range("D1:I100").ClearContents
Application.ScreenUpdating = False
Sheets("5").Columns("D:I").EntireColumn.Hidden = False
While Range("B" & lRij).Value <> ""
Workbooks.Open "H:\Ontwikkeling\(Standaard structuur)\20 Engineering\Prijslijsten\Prijslijsten matrices\" & Range("B" & lRij).Value & ".xls"
For n = ThisWorkbook.Worksheets("5").Range("C" & lzRij).Value = 1 To 100
With Workbooks(Workbooks.Count).Worksheets(1).Range("Q:Q")
Set G = .Find(ThisWorkbook.Worksheets("5").Range("C" & lzRij).Value, LookIn:=xlValues, lookat:=xlWhole)
If Not G Is Nothing Then
ThisWorkbook.Worksheets("5").Cells(lzRij, "I").End(xlToLeft).Offset(, 1) = Workbooks(Workbooks.Count).Worksheets(1).Range("R" & G.Row)
End If
End With
lzRij = lzRij + 1
Next n
Workbooks(Workbooks.Count).Close savechanges:=False
lzRij = 1
lRij = lRij + 1
Wend
Sheets("5").Columns("D:I").EntireColumn.Hidden = True
Application.ScreenUpdating = True

Select Case Application.WorksheetFunction.CountA(Range("B5:B10"))
Case 1:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(D1="""",""MATRIX"",""""))", "", "")
Case 2:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(E1<>"""","""",D1)=0,""MATRIX"",IF(E1<>"""","""",D1)))", "", "")
Case 3:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(F1<>"""","""",IF(E1="""",D1,D1&""-""&E1))=0,""MATRIX"",IF(F1<>"""","""",IF(E1="""",D1,D1&""-""&E1))))", "", "")
Case 4:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(G1<>"""","""",IF(E1="""",D1,D1&""-""&IF(F1<>"""",F1,E1)))=0,""MATRIX"",IF(G1<>"""","""",IF(E1="""",D1,D1&""-""&IF(F1<>"""",F1,E1)))))", "", "")
Case 5:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(H1<>"""","""",IF(E1="""",D1,D1&""-""&IF(G1<>"""",G1,IF(F1<>"""",F1,E1))))=0,""MATRIX"",IF(H1<>"""","""",IF(E1="""",D1,D1&""-""&IF(G1<>"""",G1,IF(F1<>"""",F1,E1))))))", "", "")
Case 6:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(I1<>"""","""",IF(E1="""",D1,D1&""-""&IF(H1<>"""",H1,IF(G1<>"""",G1,IF(F1<>"""",F1,E1)))))=0,""MATRIX"",IF(I1<>"""","""",IF(E1="""",D1,D1&""-""&IF(H1<>"""",H1,IF(G1<>"""",G1,IF(F1<>"""",F1,E1)))))))", "", "")



End Select

End Sub


Iemand enig idee hoe ik dit bestand zou kunnen verkleinen?
of is deze bestandsgrootte wel normaal?
(het bestand zelf is vertrouwelijk en kan ik niet online zetten).

Bij voorbaat dank voor alle hulp.

met vriendelijke groet,

gammelkeunited

 

[Cobbe]

Ik vermoed dat er veel opmaak in uw werkboek zit.
Als dat zo is zou je dit moeten aanpassen aan het benodigde bereik.
Je kunt daarna de ASAP Utilities downloaden en er de lastcell mee vasstellen.
En je kunt ermee alle onnodige(niet gebruikte) opmaken weghalen.

Succes, Cobbe

 

[gammelkeunited]

Hallo Cobbe,

Ik heb de ASAP Utillities geiinstalleerd
daarmee heb ik via 4. sheets- 19.remove all unused styles from every sheet
de sheets opgeschoond, er werden 8 styles gevonden waarvan er 2 verwijderd werden. echter het bestand is er nauwelijks kleiner op geworden.

misschien nog andere ideeen?

Gr.

Gammelkeunited

 

[Cobbe]

Maar heb je de celopmaak enkel toegepast op het gebruikte bereik.?
Dus ALLE opmaak voor bepaalde rijen en bepaalde kolommen toepassen.

Je kunt op elk sheet via Ctrl-End kijken of de laatste cel ook werkelijk de laatst nodige cel is.

Mvgr. Cobbe

 

[Wigi]

Volgens mij, op basis van de info die we hier krijgen, is er iets corrupt in die file. Al kan ik mij ook vergissen, ik heb het bestand niet voor mij.

En je gaat het niet graag horen, maar de enige manier om dit uit te zoeken en op te lossen, is de sheets 1 voor 1 deleten, bestand opslaan (met een andere naam), en file size vergelijken. Is het niet drastisch kleiner, dan weet je dat het niet aan die sheet lag.

Daarbij dus:

Je kunt op elk sheet via Ctrl-End kijken of de laatste cel ook werkelijk de laatst nodige cel is.

Of de cellen op de sheets die je effectief gebruikt, eens kopiëren naar een nieuw leeg bestand (dus blad per blad werken).

Wigi

 

[snb]

De code van die 40 knoppen kan vervangen worden door 1 keer die code, als die in een afzonderlijkge macro-module wordt opgeslagen.
De koppeling kan dan naar 40 knoppen gelegd worden.
Nog eenvoudiger is om één aparte werkbalk te maken voor dit werkboek met daarin een knop die de macro uitvoert: bespaart 39 keer code en 39 objekten.

En in de code:

Vervang:

Select Case Application.WorksheetFunction.CountA(Range("B5:B10"))
Case 1:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(D1="""",""MATRIX"",""""))", "", "")
Case 2:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(E1<>"""","""",D1)=0,""MATRIX"",IF(E1<>"""","""",D1)))", "", "")
Case 3:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(F1<>"""","""",IF(E1="""",D1,D1&""-""&E1))=0,""MATRIX"",IF(F1<>"""","""",IF(E1="""",D1,D1&""-""&E1))))", "", "")
Case 4:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(G1<>"""","""",IF(E1="""",D1,D1&""-""&IF(F1<>"""",F1,E1)))=0,""MATRIX"",IF(G1<>"""","""",IF(E1="""",D1,D1&""-""&IF(F1<>"""",F1,E1)))))", "", "")
Case 5:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(H1<>"""","""",IF(E1="""",D1,D1&""-""&IF(G1<>"""",G1,IF(F1<>"""",F1,E1))))=0,""MATRIX"",IF(H1<>"""","""",IF(E1="""",D1,D1&""-""&IF(G1<>"""",G1,IF(F1<>"""",F1,E1))))))", "", "")
Case 6:
Range("K1:K100").Formula = _
Replace("=IF(C1="""","""",IF(IF(I1<>"""","""",IF(E1="""",D1,D1&""-""&IF(H1<>"""",H1,IF(G1<>"""",G1,IF(F1<>"""",F1,E1)))))=0,""MATRIX"",IF(I1<>"""","""",IF(E1="""",D1, D1&""-""&IF(H1<>"""",H1,IF(G1<>"""",G1,IF(F1<>"""",F1,E1)))))))", "", "")

door in 6 cellen (bijv. K1 t/m K6) de verschillende formules te zetten.
dan is daarna slechts 1 regel code nodig:

Range("K1:K100").Formula =cells(range("B5:B10").specialcells(xlcelltypeconstants).count,11).formula

 

[gammelkeunited]

Hallo Mensen,

Allemaal hartelijk bedankt voor jullie hulp.
uiteindelijk heeft de oplossing van Wigi uitkomst gebracht. de laatste 10 sheets in het bestand bleken erg veel geheugen te kosten. waardoor dit werd veroorzaakt is mij nog onduidelijk, zelfs na het verwijderen van alle gegevens en opmaak , bleef een sheet een aantal mb's groot.
ik heb de betreffende sheets opnieuw gemaakt, nu is het complete bestand nog slechts 3mb groot wat weer lekker snel werkt.

Allen Bedankt!!

met vriendelijke groeten,

gammelkeunited

 

[Wigi]

uiteindelijk heeft de oplossing van Wigi uitkomst gebracht.

Een berekende gok op basis van mijn ervaring

Goed dat het nu weer beter loopt!