0166-Dica VBA: Método SpecialCells

O método SpecialCells é útil se queremos rapidamente “selecionar” grupos de células com características em comum.

Imagine que você precisa destacar numa planilha as células que contém fórmulas com a cor vermelha e as que contém constantes (textos, números, lógicas e erros) com a cor azul.

Normalmente iríamos criar um código que percorre-se todas as células preenchidas da planilha e testando-as, uma por vez, modificaríamos a propriedade Interior.Color com base no teste se conteúdo é uma fórmula ou uma constante.

Algo como este código aqui:

Sub ColorirCelulasV01()
For Each Célula In ActiveSheet.UsedRange
    With Célula
    If .HasFormula Then
    .Interior.ColorIndex = 3
    ElseIf .Value  "" Then .Interior.ColorIndex = 5
    End If
    End With
Next Célula
End Sub

Sem dúvida irá funcionar, mas que tal fazermos isso mais rápido?

Utilizando SpecialCells vamos informar ao Excel para executar o comando de uma só vez nas células que contém fórmulas e em seguida nas células que contém constantes.

Observem como ficaria o código:

Sub ColorirCelulasV02()
    With ActiveSheet.UsedRange
    .SpecialCells(xlCellTypeConstants, 23).Interior.ColorIndex = 3
    .SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex = 5
    End With
End Sub

Criei uma planilha com 1.000 linhas e 32 colunas preenchidas com diferentes valores e fórmulas.
A 1ª versão do código demorou 3,20 segundos para ser executada e a 2ª versão 0,15 segundos.
Pode parecer pouco em números absolutos, mas significa que a 2ª versão foi cerca de 21 vezes mais rápida!

Agora vamos usar as duas abordagens acima, para escrever um código que percorra um intervalo de células (A1:D50) e preencha as células vazias com a expressão “Sem valor”.

Para o primeiro código iremos precisar de 2 loops (um para percorrer as linhas e outro as colunas) e um teste para verificarmos o conteúdo da célula:

Sub PreencherCelulasVaziasV01()
    For i = 1 To 50
        For j = 1 To 4
            If ActiveSheet.Cells(i, j) = "" Then ActiveSheet.Cells(i, j) = "Sem valor"
        Next j
    Next i
End Sub

Para o segundo código vamos simplesmente indicar que queremos apenas as células vazias do intervalo:

Sub PreencherCelulasVaziasV02()
[A1:D50].SpecialCells(xlCellTypeBlanks) = "Sem Valor"
End Sub

Uma outra utilidade para a “seleção” de células vazias é a eliminação de linhas sem conteúdo.

No exemplo a seguir, são excluídas as linhas que correspondem a células vazias no intervalo A1:A100

Sub EliminarLinhasVazias()
[A1:A100].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

OBS: Não recomendo que utilizem esta abordagem se o arquivo for muito pesado, ou se quantidade de linhas a serem excluídas for muito grande, pois o Excel pode travar devido ao consumo excessivo de memória. Veja outras alternativas em  0054-Eliminando linhas em branco

Embora SpecialCells permita selecionar as células vazias do intervalo, não há nenhuma forma direta de indicar todas as células preenchidas. Isso porque só é possível indicar separadamente fórmulas e constantes.

Mas podemos contornar este problema utilizando o método Union para unir os dois diferentes conjuntos de células.

Tomemos como exemplo o código a seguir, no qual todas as células preenchidas da planilha terão sua fonte definida como Arial, tamanho 10, na cor Azul:

Sub FormatarFonte()

'Criar o intervalo rg como união do intervalo que contém as fórmulas e
'do intervalo que contém as constantes
With ActiveSheet.UsedRange
    Set rg = Union(.SpecialCells(xlCellTypeConstants, 23), .SpecialCells(xlCellTypeFormulas, 23))
End With

'Modificar as propriedades das fontes do intervalo
With rg.Font
    .Size = 10
    .ColorIndex = 5
    .Name = "Arial"
End With

End Sub

SpecialCells é mais um recurso que vale a pena ter conhecimento para criar códigos diferenciados.

Abraços e até a próxima!

In Excel We Trust

Marcado com: , , , , , , ,
Publicado em Nível: Avançado
3 comentários em “0166-Dica VBA: Método SpecialCells
  1. Osmar disse:

    muito bom parabens, Adilson

  2. frs disse:

    Como faço para somente deletar valores vazios da celula de uma coluna

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,969,548

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.477 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: