Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
Arquivo passou de 1...
 
Notifications
Clear all

Arquivo passou de 105kB para 27MB!!!!

 
2024 - VBA & Macros
2 Posts
2 Usuários
0 Reactions
572 Visualizações
RSS
 Ismael2017
(@ismael2017)
Posts: 1
New Member
Topic starter
 

Prezados, tenho um arquivo que faz a busca de informações coladas em uma aba e organiza os dados em outra aba. Porém quando incluí algumas restrições na busca (criei uma lista em outra aba) e incluí uma lista de endereços nesta busca (criei outra aba) o arquivo ficou absurdamente grande. Passou de uns poucos kB para 27MB.

Alguém consegue me dar uma dica do que pode ter acontecido?
Segue o código antes e depois:

MACRO ANTES DA MUDANÇA:

Sub EXECUTAR()
'
' EXECUTAR Macro

    Application.ScreenUpdating = False
    
    Sheets("COLAR").Select
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "General"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=""2"","""",IF(RC[-2]="""",RC[-4],IF(RC[-2]<>R[-1]C[-2],RC[-4],MID(R[-1]C,1,4)&RC[-4])))"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F650")
    Range("F2:F650").Select
    ActiveWindow.SmallScroll Down:=-15
    Columns("K:K").Select
    Selection.NumberFormat = "General"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "1100"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],C6:C9,4,0)"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/R1C11"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K650")
    Range("K2:K627").Select
    ActiveWindow.SmallScroll Down:=-9
    Sheets("LISTA").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,COLAR!C6:C11,2,0)"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,COLAR!C6:C11,6,0)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,COLAR!C6:C11,3,0)"
    Range("D3:F3").Select
    Selection.Copy
    Range("D3:F650").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("D3:F650").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("COLAR").Select
    Range("A2:J2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "COLAR AQUI"
    Range("A2").Select
    Sheets("LISTA").Select
    Range("D3").Select

    Columns("D:F").Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Rows("2:2").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="<>", Operator:=xlAnd, _
        Criteria2:="<>0"
        
    Range("D2").Select

    Application.ScreenUpdating = True

End Sub
Sub LIMPAR()
'
' LIMPAR Macro


'
    Rows("2:2").Select
    Selection.AutoFilter
       
    Range("D3:F650").Select
    Selection.ClearContents
    Range("D2").Select
End Sub

MACRO DESPOIS DA MUDANÇA:

Sub EXECUTAR1()
EXECUTAR
POSIÇÃO
EXCLUIR2


End Sub
Sub EXECUTAR()
'
' EXECUTAR Macro


'
    Application.ScreenUpdating = False
    
    Sheets("COLAR").Select
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "General"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=""2"","""",IF(RC[-2]="""",RC[-4],IF(RC[-2]<>R[-1]C[-2],RC[-4],MID(R[-1]C,1,4)&RC[-4])))"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F650")
    Range("F2:F650").Select
    ActiveWindow.SmallScroll Down:=-15
    Columns("K:K").Select
    Selection.NumberFormat = "General"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "9000"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],C6:C9,4,0)"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/R1C11"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K650")
    Range("K2:K650").Select
    ActiveWindow.SmallScroll Down:=-9
    Sheets("LISTA").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,COLAR!C6:C11,2,0)"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,COLAR!C6:C11,6,0)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,COLAR!C6:C11,3,0)"
    Range("D3:F3").Select
    Selection.Copy
    Range("D3:F650").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("D3:F650").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("COLAR").Select
    Range("A2:J2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "COLAR AQUI"
    Range("A2").Select
    Sheets("LISTA").Select
    Range("D3").Select

    Columns("D:F").Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Rows("2:2").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="<>", Operator:=xlAnd, _
        Criteria2:="<>0"
        
    Range("D2").Select
    

    Application.ScreenUpdating = True

End Sub

Sub POSIÇÃO()
'
    Sheets("LISTA").Select
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Lista,6,FALSE)"
    Range("G3").Select
    Selection.AutoFill Destination:=Range("G3:G650"), Type:=xlFillDefault
    Range("G3:G650").Select
    
End Sub


Sub EXCLUIR2()

    Rows("2:2").Select
    Range("C2").Activate
    Selection.AutoFilter Field:=4, Criteria1:="<>", Operator:=xlAnd, _
            Criteria2:="<>0"
    Range("C3").Select
End Sub


Sub LIMPAR()
'
' LIMPAR Macro

'
    Rows("2:2").Select
    Range("C2").Activate
    Selection.AutoFilter
       
    Range("D3:H650").Select
    Selection.ClearContents
    Range("D2").Select
    Application.ScreenUpdating = True
    
End Sub
 
Postado : 18/04/2017 9:15 am
Fernando Fernandes
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
 

Sua pasta de trabalho de repente cresceu demais?

Leia o artigo abaixo:
http://www.planilhando.com.br/forum/viewtopic.php?f=28&t=8862

E depois de ler, reveja sua macro e identifique pontos onde você pode estar fazendo o que o artigo propôe!

E como seu código é enorme, eu sugiro que coloque somente as linhas que foram alteradas... fica mais fácil pra gente te ajudar..

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

 
Postado : 18/04/2017 9:21 am
Forum Jump:
  Tópico Anterior
Próximo Tópico