VBA Excel - Como deixar suas planilhas enxutas

Confesso que manter o tamanho das planilhas sejam um tema recorrente e difícil de divulgar. Certamente causa interesse, mas o ponto principal é: Como deixá-las menores?

Ja abordei este assunto diversas vezes, sobre óticas distintas: Explicando o motivo destas ficarem assim, mostrando ações efetivas de torná-las menores, demonstrei técnicas com o VBA e tudo o mais.

Nos idos de 2009, escrevi coisas similares as demonstradas abaixo:

Possivelmente já percebeu como suas planilhas inflam sem qualquer aparente explicação, como se tivesse comido vários quilos de açúcar. De uma hora prá outra o que tinha apenas uns parcos 530 KB de peso, passa a ter e exibir exuberantes 2.350 KB. O que aconteceu? Será que nossas planilhas atacam a geladeira durante a madrugada?

Com o passar do tempo, sem que necessariamente tenhamos acrescentado algum conteúdo relevante, nossas planilhas persistem em aumentar.

Linhas inofensivas

:

É muito comum que somente abramos o nosso arquivo, efetuando pequenas alterações em certas células, além de vez ou outra inserirmos algumas linhas em branco, apenas para posicionarmos algumas informações. O que talvez não percebamos é que estas inofensivas linhas em branco não somem, antes são salvas, ocupando espaço desnecessário. Lógico que isso é muito comum devido a grande manipulação de dados que efetuamos diariamente. Nunca paramos prá pensar em tamanho no nosso dia-a-dia. Somente quando não temos espaço, ou quando o administrador da rede diz-nos que nosso espaço no Public está lotado é que pensamos no motivo de tão poucas planilhas ocuparem tanto espaço.

Mandando suas planilhas para o SPA

:

O MS Excel não consegue distinguir as linhas que estão em branco (pelo menos não o fazia corretamente na versão de 2009) ou vazias e acaba por gravar todas as ocorrências em branco como conteúdo das nossas planilhas. Além disso, caso formatemos uma coluna inteira até a linha 1 milhão (mesmo que sem querer), ao fechar a planilhas teremos gravado aquela coluna em um milhão de linhas, apenas porque esquecemos da formatação ali.

O tamanho das planilhas é uma preocupação constante nas nossas aplicações. A não ser que realmente precisemos ter ocorrências extensas, deveríamos automatizar a deleção das linhas excedentes.

Como faremos isso?

Técnicas de exclusão de linhas podem ser colocadas ao fecharmos e/ou gravarmos nossas planilhas...Isso as fariam efetuar atividades 'aeróbicas' constantemente, queimando suas calorias extras...

Deletar linhas e efetuar as tais tarefas aeróbicas podem tornar o fechamento das planilhas muito lento dependendo do tamanho.

Confesso que nesta época era persistente, por isso não parei por aí:

Quando nossas planilhas recebem dados de outras bases (fontes), seja através de ODBC, Macros ou mesmo informações da Internet, não raro aparecem melhares de linhas em branco. Caso ache útil deletá-las poderá implementar a solução abaixo.

Function EliminateThousandBlankLines(StarLine as Long)

' Author: André Luiz Bernardes.

'    Date: 05.02.2009

    Let nRow = StartLine

    Do While ActiveSheet.Cells(nRow, 1) <> ""

        If ActiveSheet.Cells(nRow, 1).Value <> strUserName Then

            ActiveSheet.Rows(nRow).EntireRow.Delete

        Else

            Let nRow = nRow + 1

        End If

    Loop

End Function

Nesta missão de suma importância, onde a deleção de linhas e colunas desnecessárias mostram-se tão preementes, deixo-vos códigos utilíssimos:

Function LastRow (nColumn As String, InitLine As Single) As Single

    ' Author:                     Date:               Contact:

    ' André Bernardes             16/09/2008 14:20    bernardess@gmail.com

    ' Retorna o número de ocorrências.

    Dim nLine As Single

    Dim nStart As Single

    Dim nFinito As Single

    Dim Cabessalho As Single

    Dim nCeo As String

    Application.Volatile

    Let nStart = InitLine + 1

    Let nFinito = 65000

    Let Cabessalho = InitLine

    Do While nStart < nFinito

        Let nCeo = nColumn & Trim(Str(nStart))

        If Application.ActiveSheet.Range(nCeo).Value = "" Then

            Exit Do

        End If

        'Let Application.StatusBar.Value = " Linha: " & nStart

        Let nStart = nStart + 1

    Loop

    Let LastRow = (nStart - 1) '- Cabeçalho

    'Let Application.StatusBar.Value = "  "

End Function

Function FindLastColumn() As Single

    Let FindLastColumn = Range("IV10").End(xlToLeft).Column

    

    'ActiveSheet.UsedRange.Columns.Count

    'Dim LastCol As Integer

    

    'Let LastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

    'Let FindLastColumn = LastCol

End Function

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)

NomePlanilha = ActiveSheet.Name

NomePasta = ActiveWorkbook.Name

Endereco = ActiveWorkbook.Path

UsuarioExcel = Application.UserName

UsuarioEstacao = Environ("USERNAME")

UltimaLinhaNumero = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

UltimaColunaNumero = ActiveCell.SpecialCells(xlCellTypeLastCell).Column

UltimaCelulaRange = ActiveCell.SpecialCells(xlCellTypeLastCell).Address & " ou " & ActiveCell.SpecialCells(xlCellTypeLastCell).Address(RowAbsolute:=False) & " ou " & ActiveCell.SpecialCells(xlCellTypeLastCell).Address(ReferenceStyle:=xlR1C1)

CelulaAtualLinhaNumero = ActiveCell.Row

CelulaAtualColunaNumero = ActiveCell.Column

CelulaAtualRange = ActiveCell.Address & " ou " & ActiveCell.Address(RowAbsolute:=False) & " ou " & ActiveCell.Address(ReferenceStyle:=xlR1C1)

CelulaAtualRelativo = ActiveCell.Address(ReferenceStyle:=xlR1C1, RowAbsolute:=False, ColumnAbsolute:=False, RelativeTo:=Worksheets(NomePlanilha).Cells(1, 1))

MsgBox "Planilha Atual" & vbCrLf & vbCrLf & _

       vbTab & "Nome" & vbTab & vbTab & ": " & NomePlanilha & vbCrLf & _

       vbTab & "Última Linha" & vbTab & ": " & UltimaLinhaNumero & vbCrLf & _

       vbTab & "Última Coluna" & vbTab & ": " & UltimaColunaNumero & vbCrLf & _

       vbTab & "Última Célula" & vbTab & ": " & UltimaCelulaRange & vbCrLf & vbCrLf & vbCrLf & _

       "Célula Atual" & vbCrLf & vbCrLf & _

       vbTab & "Número da Linha" & vbTab & ": " & CelulaAtualLinhaNumero & vbCrLf & _

       vbTab & "Número da Coluna" & vbTab & ": " & CelulaAtualColunaNumero & vbCrLf & _

       vbTab & "Range" & vbTab & vbTab & ": " & CelulaAtualRange & vbCrLf & _

       vbTab & "Relativo a A1" & vbTab & ": " & CelulaAtualRelativo & vbCrLf & vbCrLf & vbCrLf & _

       "Outras Informações" & vbCrLf & vbCrLf & _

       "Nome da Pasta de Trabalho" & vbTab & vbTab & ": " & NomePasta & vbCrLf & _

       "Caminho da Pasta de Trabalho" & vbTab & ": " & Endereco & vbCrLf & _

       "Usuario (Excel)" & vbTab & vbTab & vbTab & ": " & UsuarioExcel & vbCrLf & _

       "Usuario (Estação)" & vbTab & vbTab & vbTab & ": " & UsuarioEstacao, vbInformation, "Informações"

End Sub

Referência: Canguru

Tags: Excel, delete, rows, lines, excel size, cell, cell info, worksheet, small workbook

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

bernardes@.com