✔ VBA Excel Specialist®: 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