Excluir linhas com base em um valor de célula (ou condição) no Excel (guia fácil)

Ao trabalhar com grandes conjuntos de dados, pode ser necessário excluir rapidamente as linhas com base nos valores das células (ou com base em uma condição).

Por exemplo, considere os seguintes exemplos:

1. Você tem dados de representante de vendas e deseja excluir todos os registros de uma região ou produto específico.
2. Você deseja excluir todos os registros em que o valor de venda seja inferior a 100.
3. Você deseja excluir todas as linhas em que haja uma célula em branco.

Existem várias maneiras de transformar esse gato de dados no Excel.

O método que você escolher para excluir as linhas dependerá de como seus dados são estruturados e qual é o valor ou condição da célula com base na qual você deseja excluir essas linhas.

Neste tutorial, mostrarei várias maneiras de excluir linhas no Excel com base em um valor de célula ou uma condição.

Filtrar linhas com base no valor / condição e, em seguida, excluí-lo

Uma das maneiras mais rápidas de excluir linhas que contêm um valor específico ou atendem a uma determinada condição é filtrá-los. Depois de ter os dados filtrados, você pode excluir todas essas linhas (enquanto as linhas restantes permanecem intactas).

O filtro do Excel é bastante versátil e você pode filtrar com base em muitos critérios (como texto, números, datas e cores)

Vejamos dois exemplos onde você pode filtrar as linhas e excluí-las.

Excluir linhas que contenham um texto específico

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja excluir todas as linhas onde a região é Centro-Oeste (na Coluna B).

Embora neste pequeno conjunto de dados você possa optar por excluir essas linhas manualmente, muitas vezes seus conjuntos de dados serão enormes, onde excluir as linhas manualmente não será uma opção.

Nesse caso, você pode filtrar todos os registros onde a região é Centro-Oeste e, em seguida, excluir todas essas linhas (enquanto mantém as outras linhas intactas).

Abaixo estão as etapas para excluir linhas com base no valor (todos os registros do Centro-Oeste):

1. Selecione qualquer célula no conjunto de dados da qual deseja excluir as linhas
2. Clique na guia Dados
3. No grupo ‘Classificar e Filtrar’, clique no ícone Filtro. Isso aplicará filtros a todas as células de cabeçalhos no conjunto de dados
4. Clique no ícone Filtro na célula do cabeçalho Região (este é um pequeno ícone de triângulo apontando para baixo no canto superior direito da célula)
5. Desmarque todas as outras opções, exceto a opção Centro-Oeste (uma maneira rápida de fazer isso é clicando na opção Selecionar Tudo e, em seguida, clicando na opção Centro-Oeste). Isso filtrará o conjunto de dados e mostrará apenas os registros da região Centro-Oeste.
6. Selecione todos os registros filtrados
7. Clique com o botão direito em qualquer uma das células selecionadas e clique em ‘Excluir linha’
8. Na caixa de diálogo que se abre, clique em OK. Neste ponto, você não verá nenhum registro no conjunto de dados.
9. Clique na guia Dados e clique no ícone Filtro. Isso removerá o filtro e você verá todos os registros, exceto os excluídos.

As etapas acima primeiro filtram os dados com base em um valor de célula (ou pode ser outra condição, como depois / antes de uma data ou maior / menor que um número). Assim que tiver os registros, basta excluí-los.

Alguns atalhos úteis que você deve conhecer para acelerar o processo:

1. Control + Shift + L para aplicar ou remover o filtro
2. Control + - (segure a tecla Ctrl e pressione a tecla menos) para excluir as células / linhas selecionadas

No exemplo acima, eu tinha apenas quatro regiões distintas e podia selecionar e desmarcar manualmente na lista Filtro (nas etapas 5 acima).

Caso você tenha muitas categorias / regiões, pode digitar o nome no campo logo acima da caixa (que contém os nomes das regiões) e o Excel mostrará apenas os registros que correspondem ao texto digitado (conforme mostrado abaixo). Assim que tiver o texto com base no qual deseja filtrar, pressione a tecla Enter.

Observe que quando você exclui uma linha, tudo o que você pode ter em outras células nessas linhas será perdido. Uma maneira de contornar isso é criar uma cópia dos dados em outra planilha e excluir as linhas nos dados copiados. Uma vez feito isso, copie-o de volta no lugar dos dados originais.

Ou

Você pode usar os métodos mostrados posteriormente neste tutorial (usando o método Sort ou Find All Method)

Excluir linhas com base em uma condição numérica

Assim como usei o método de filtro para excluir todas as linhas que contêm o texto Centro-Oeste, você também pode usar uma condição de número (ou uma condição de data).

Por exemplo, suponha que eu tenha o conjunto de dados abaixo e desejo excluir todas as linhas em que o valor de venda é inferior a 200.

Abaixo estão as etapas para fazer isso:

1. Selecione qualquer célula nos dados
2. Clique na guia Dados
3. No grupo ‘Classificar e Filtrar’, clique no ícone Filtro. Isso aplicará filtros a todas as células de cabeçalhos no conjunto de dados
4. Clique no ícone Filtro na célula do cabeçalho Vendas (este é um pequeno ícone de triângulo apontando para baixo no canto superior direito da célula)
5. Passe o cursor sobre a opção Filtros de número. Isso mostrará todas as opções de filtro relacionadas a números no Excel.
6. Clique na opção ‘Menor que’.
7. Na caixa de diálogo ‘Custom Autofilter’ que se abre, insira o valor ‘200’ no campo
8. Clique OK. Isso filtrará e mostrará apenas os registros em que o valor das vendas é inferior a 200
9. Selecione todos os registros filtrados
10. Clique com o botão direito em qualquer uma das células e clique em Excluir linha
11. Na caixa de diálogo que se abre, clique em OK. Neste ponto, você não verá nenhum registro no conjunto de dados.
12. Clique na guia Dados e clique no ícone Filtro. Isso removerá o filtro e você verá todos os registros, exceto os excluídos.

Existem muitos filtros de número que você pode usar no Excel - como menor que / maior que, igual / diferente, entre, 10 primeiros, acima ou abaixo da média, etc.

Nota: Você também pode usar vários filtros. Por exemplo, você pode excluir todas as linhas em que o valor de vendas é maior que 200, mas menor que 500. Nesse caso, você precisa usar duas condições de filtro. A caixa de diálogo Custom Autofilter permite ter dois critérios de filtro (AND e OR).

Assim como os filtros de número, você também pode filtrar os registros com base na data. Por exemplo, se você deseja remover todos os registros do primeiro trimestre, você pode fazer isso usando as mesmas etapas acima. Quando você está trabalhando com filtros de data, o Excel mostra automaticamente os filtros relevantes (conforme mostrado abaixo).

Embora a filtragem seja uma ótima maneira de excluir linhas rapidamente com base em um valor ou condição, ela tem uma desvantagem - exclui a linha inteira. Por exemplo, no caso abaixo, ele excluiria todos os dados que estão à direita do conjunto de dados filtrado.

E se eu só quiser excluir os registros do conjunto de dados, mas quiser manter os dados restantes intactos.

Você não pode fazer isso com filtragem, mas pode fazer isso com classificação.

Classifique o conjunto de dados e, em seguida, exclua as linhas

Embora a classificação seja outra maneira de excluir linhas com base no valor, na maioria dos casos, é melhor usar o método de filtro abordado acima.

Essa técnica de classificação é recomendada apenas quando você deseja excluir as células com os valores e não as linhas inteiras.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja excluir todos os registros onde a região é Centro-Oeste.

Abaixo estão as etapas para fazer isso usando classificação:

1. Selecione qualquer célula nos dados
2. Clique na guia Dados
3. No grupo Classificar e Filtrar, clique no ícone Classificar.
4. Na caixa de diálogo Classificar que é aberta, selecione Região na coluna classificar por.
5. Na opção Classificar em, certifique-se de que Valores de Célula esteja selecionado
6. Na opção Ordem, selecione A a Z (ou Z a A, realmente não importa).
7. Clique OK. Isso lhe dará o conjunto de dados classificado conforme mostrado abaixo (classificado pela coluna B).
8. Selecione todos os registros com a região Centro-Oeste (todas as células nas linhas, não apenas a coluna da região)
9. Uma vez selecionado, clique com o botão direito e clique em Excluir. Isso abrirá a caixa de diálogo Excluir.
10. Certifique-se de que a opção ‘Mudar células para cima’ esteja selecionada.
11. Clique OK.

As etapas acima excluiriam todos os registros em que a região era Centro-Oeste, mas não excluem a linha inteira. Portanto, se você tiver dados à direita ou à esquerda do conjunto de dados, eles permanecerão ilesos.

No exemplo acima, classifiquei os dados com base no valor da célula, mas você também pode usar as mesmas etapas para classificar com base em números, datas, cor da célula ou cor da fonte, etc.

Aqui está um guia detalhado sobre como classificar dados no Excel

No caso de desejar manter a ordem do conjunto de dados original, mas remover os registros com base em critérios, você precisa encontrar uma maneira de ordenar os dados de volta ao original. Para fazer isso, adicione uma coluna com números de série antes de classificar os dados. Quando terminar de excluir as linhas / registros, basta classificar com base nesta coluna extra que você adicionou.

Encontre e selecione as células com base no valor da célula e, em seguida, exclua as linhas

O Excel tem uma funcionalidade Localizar e Substituir que pode ser excelente quando você deseja localizar e selecionar células com um valor específico.

Depois de selecionar essas células, você pode excluir facilmente as linhas.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja excluir todas as linhas onde a região é Centro-Oeste.

Abaixo estão as etapas para fazer isso:

1. Selecione todo o conjunto de dados
2. Clique na guia Home
3. No grupo Editing, clique na opção ‘Find & Select’ e, em seguida, clique em Find (você também pode usar o atalho de teclado Control + F).
4. Na caixa de diálogo Localizar e substituir, insira o texto ‘Centro-Oeste’ no campo ‘Encontrar o quê:’.
5. Clique em Encontrar tudo. Isso mostrará instantaneamente todas as instâncias do texto Centro-Oeste que o Excel foi capaz de encontrar.
6. Use o atalho de teclado Control + A para selecionar todas as células que o Excel encontrou. Você também poderá ver todas as células selecionadas no conjunto de dados.
7. Clique com o botão direito em qualquer uma das células selecionadas e clique em Excluir. Isso abrirá a caixa de diálogo Excluir.
8. Selecione a opção ‘Linha inteira’
9. Clique OK.

As etapas acima excluiriam todas as células em que o valor da região é Centro-Oeste.

Observação: como Localizar e substituir pode lidar com caracteres curinga, você pode usá-los ao localizar dados no Excel. Por exemplo, se você deseja excluir todas as linhas onde a região é Centro-Oeste ou Sudoeste, você pode usar '*Oeste‘Como o texto a ser encontrado na caixa de diálogo Localizar e substituir. Isso fornecerá todas as células em que o texto termina com a palavra Oeste.

Excluir todas as linhas com uma célula em branco

No caso de desejar excluir todas as linhas onde há células em branco, você pode fazer isso facilmente com uma funcionalidade embutida no Excel.

É o Células Especiais Go-To opção - que permite selecionar rapidamente todas as células em branco. E depois de selecionar todas as células em branco, excluí-las é muito simples.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e eu queira excluir todas as linhas onde não tenho o valor de venda.

Abaixo estão as etapas para fazer isso:

1. Selecione o conjunto de dados inteiro (A1: D16 neste caso).
2. aperte o F5 chave. Isso abrirá a caixa de diálogo ‘Ir para’ (você também pode obter esta caixa de diálogo em Início -> Edição -> Localizar e selecionar -> Ir para).
3. Na caixa de diálogo ‘Ir para’, clique no botão Especial. Isso abrirá a caixa de diálogo ‘Ir para o especial’
4. Na caixa de diálogo Go To Special, selecione ‘Blanks’.
5. Clique OK.

As etapas acima selecionariam todas as células que estão em branco no conjunto de dados.

Depois de selecionar as células em branco, clique com o botão direito em qualquer uma das células e clique em Excluir.

Na caixa de diálogo Excluir, selecione a opção ‘Linha inteira’ e clique em OK. Isso excluirá todas as linhas que contêm células em branco.

Se você estiver interessado em aprender mais sobre essa técnica, escrevi um tutorial detalhado sobre como excluir linhas com células em branco. Inclui o método 'Go To Special', bem como um método VBA para excluir linhas com células em branco.

Filtrar e excluir linhas com base no valor da célula (usando VBA)

O último método que vou mostrar a você inclui um pouco de VBA.

Você pode usar este método se frequentemente precisar excluir linhas com base em um valor específico em uma coluna. Você pode adicionar o código VBA uma vez e adicioná-lo à sua pasta de trabalho Macro pessoal. Dessa forma, ele estará disponível para uso em todas as suas pastas de trabalho do Excel.

Este código funciona da mesma maneira que o método Filter abordado acima (exceto pelo fato de que ele executa todas as etapas no backend e economiza alguns cliques).

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja excluir todas as linhas onde a região é Centro-Oeste.

Abaixo está o código VBA que fará isso.

Sub DeleteRowsWithSpecificText () 'Fonte: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible) .Delete End Sub

O código acima usa o método VBA Autofilter para primeiro filtrar as linhas com base nos critérios especificados (que é ‘Centro-Oeste’), em seguida, selecione todas as linhas filtradas e exclua-as.

Observe que usei Offset no código acima para garantir que minha linha de cabeçalho não seja excluída.

O código acima não funciona se seus dados estiverem em uma tabela do Excel. A razão para isso é que o Excel considera uma Tabela do Excel como um objeto de lista. Portanto, se você deseja excluir linhas que estão em uma Tabela, é necessário modificar um pouco o código (abordado posteriormente neste tutorial).

Antes de excluir as linhas, ele mostrará um prompt conforme mostrado abaixo. Acho isso útil porque me permite verificar novamente a linha filtrada antes de excluí-la.

Lembre-se de que, ao excluir linhas usando o VBA, não é possível desfazer essa alteração. Portanto, use isso apenas quando tiver certeza de que funciona da maneira que deseja. Além disso, é uma boa ideia manter uma cópia de backup dos dados para o caso de algo dar errado.

Caso seus dados estejam em uma tabela do Excel, use o código abaixo para excluir linhas com um valor específico:

Sub DeleteRowsinTables () 'Fonte: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter Field: = 2, Criteria1: = "Centro-Oeste" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete End Sub

Como o VBA considera a Tabela do Excel como um objeto de lista (e não um intervalo), tive que alterar o código de acordo.

Onde colocar o código VBA?

Este código precisa ser colocado no backend do Editor VB em um módulo.

Abaixo estão as etapas que mostrarão como fazer isso:

1. Abra a pasta de trabalho na qual deseja adicionar este código.
2. Use o atalho de teclado ALT + F11 para abrir a janela do Editor VBA.
3. Nesta janela do Editor VBA, à esquerda, há um painel ‘Project Explorer’ (que lista todas as pastas de trabalho e objetos de planilhas). Clique com o botão direito em qualquer objeto da pasta de trabalho (na qual deseja que este código funcione), passe o cursor sobre ‘Inserir’ e clique em ‘Módulo’. Isso adicionará o objeto Módulo à pasta de trabalho e também abrirá a janela de código do módulo à direita
4. Na janela do módulo (que aparecerá à direita), copie e cole o código acima.

Depois de ter o código no Editor VB, você pode executar o código usando qualquer um dos métodos abaixo (certifique-se de ter selecionado qualquer célula no conjunto de dados em que deseja executar este código):

1. Selecione qualquer linha dentro do código e pressione a tecla F5.
2. Clique no botão Executar na barra de ferramentas do Editor VB
3. Atribua a macro a um botão ou forma e execute-a clicando nela na própria planilha
4. Adicione-o à Barra de Ferramentas de Acesso Rápido e execute o código com um único clique.

Você pode ler tudo sobre como executar o código de macro no Excel neste artigo.

Observação: como a pasta de trabalho contém um código de macro VBA, você precisa salvá-lo no formato habilitado para macro (xlsm).