Como excluir todas as outras linhas no Excel (ou cada enésima linha)

Assistir ao vídeo - Excluir todas as outras linhas no Excel

Às vezes, pode ser necessário excluir todas as outras linhas (ou seja, linhas alternadas) ou a cada terceira / quarta / quinta linha no Excel.

Um caso de uso disso pode ser quando você tem dados semanais e só deseja os dados de semanas pares ou ímpares. Outra pode ser quando você obtém um despejo de dados de um banco de dados e todos os dados úteis estão apenas em cada segunda ou terceira linha.

Embora você sempre tenha a opção de selecionar e excluir linhas manualmente, isso é ineficiente no caso de você ter um grande conjunto de dados.

Existem maneiras melhores de fazer isso.

Neste tutorial, mostrarei algumas maneiras de excluir todas as outras linhas do Excel usando uma técnica de filtragem simples. E se você está satisfeito com o uso de um código de macro VBA, também forneci um código VBA curto que faz isso com um único clique. Os métodos mostrados neste tutorial funcionam para todas as versões do Excel (2007, 2010, 2013 e 2016)

Embora a exclusão de todas as outras linhas no Excel possa ser bastante simples, pode ser um desafio excluir todas as outras colunas. Isso ocorre porque você não pode filtrar colunas no Excel (da mesma forma que você pode filtrar linhas). Na última seção deste tutorial, também mostrarei algumas maneiras de excluir colunas alternativas (ou cada enésima coluna) no Excel.

Excluir todas as outras linhas filtrando o conjunto de dados (usando fórmula)

Se você pudesse filtrar de alguma forma todas as linhas pares ou ímpares, seria muito fácil deletar essas linhas / registros.

Embora não haja nenhum recurso embutido para fazer isso, você pode usar uma coluna auxiliar para primeiro dividir as linhas em pares e ímpares e, em seguida, filtrar com base no valor da coluna auxiliar.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo, que contém dados de vendas para cada representante de vendas para duas regiões (EUA e Canadá) e deseja excluir os dados do Canadá.

Abaixo estão as etapas para primeiro filtrar e, em seguida, excluir todas as outras linhas (que contém dados para o Canadá):

1. Na célula adjacente ao cabeçalho da última coluna, insira o texto HelperColumn (ou qualquer texto de cabeçalho que você deseja que a coluna auxiliar tenha)
2. Na célula abaixo do cabeçalho da coluna auxiliar, insira a seguinte fórmula: = ISEVEN (ROW ()). Copie esta fórmula para todas as células. Esta fórmula retorna TRUE para todas as linhas pares e FALSE para todas as linhas ODD
3. Selecione o conjunto de dados inteiro (incluindo as células onde inserimos a fórmula na etapa acima).
4. Clique na guia Dados
5. Clique no ícone Filtro. Isso aplicará um filtro a todos os cabeçalhos no conjunto de dados
6. Clique no ícone Filtro na célula HelperColumn.
7. Desmarque a opção TRUE (para que apenas a opção FALSE seja selecionada)
8. Clique OK. Isso filtrará os dados e mostrará apenas os registros em que o valor HelperColumn é FALSE.
9. Selecione todas as células filtradas na coluna auxiliar (excluindo o cabeçalho)
10. Clique com o botão direito em qualquer uma das células selecionadas e clique em ‘Excluir linha’
11. Na caixa de diálogo que se abre, clique em OK. Isso excluirá todos os registros visíveis e você verá apenas a linha do cabeçalho a partir de agora.
12. Clique na guia Dados e no ícone Filtro. Isso removerá o filtro e você poderá ver todos os registros restantes.

As etapas acima filtram cada segunda linha no conjunto de dados e, em seguida, excluem essas linhas.

Não se preocupe com os valores da coluna auxiliar nesta fase. Os dados resultantes têm apenas as linhas dos EUA e todas as linhas do Canadá são excluídas. Você pode excluir a coluna auxiliar agora.

Caso você queira excluir todas as outras linhas começando da primeira linha em diante, selecione a opção VERDADEIRO na etapa 7 e desmarque a opção FALSO.

Nota: Quando você exclui as linhas no Excel usando o método acima, ele também exclui quaisquer dados que você possa ter no todo (exceto no conjunto de dados). Ao usar esse método, certifique-se de não ter nada à esquerda e à direita do conjunto de dados que você está excluindo.

Embora o método acima seja ótimo, ele tem duas desvantagens:

1. É necessário que você adicione uma nova coluna (o HelperColumn em nosso exemplo acima)
2. Pode ser demorado se você tiver que deletar linhas alternativas, faça isso com frequência

Tutorial relacionado: Excluir linhas com base em um valor de célula (ou condição) no Excel

Excluir cada enésima linha filtrando o conjunto de dados (usando fórmula)

No método acima, mostrei como excluir todas as outras linhas (linhas alternativas) no Excel.

E você também pode usar a mesma lógica para excluir cada terceira ou quarta linha do Excel.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja excluir todos a cada terceira linha.

As etapas para excluir a cada terceira linha são quase as mesmas que as cobertas na seção acima (para excluir linhas alternativas). A única diferença está na fórmula usada na Etapa 2.

Abaixo estão as etapas para fazer isso:

1. Na célula adjacente ao cabeçalho da última coluna, insira o texto HelperColumn (ou qualquer texto de cabeçalho que você deseja que a coluna auxiliar tenha)
2. Na célula abaixo do cabeçalho da coluna auxiliar, insira a seguinte fórmula: = MOD (ROW (), 3) = 1. Esta fórmula retorna TRUE para cada terceira linha e FALSE para todas as outras linhas.
3. Selecione todo o conjunto de dados (incluindo as células onde inserimos a fórmula na etapa acima).
4. Clique na guia Dados
5. Clique no ícone Filtro. Isso aplicará um filtro a todos os cabeçalhos no conjunto de dados
6. Clique no ícone Filtro na célula HelperColumn.
7. Desmarque a opção TRUE (para que apenas a opção FALSE seja selecionada)
8. Clique OK. Isso filtrará os dados e mostrará apenas os registros em que o valor HelperColumn é FALSE.
9. Selecione todas as células na coluna auxiliar
10. Clique com o botão direito em qualquer uma das células selecionadas e clique em Excluir linha
11. Na caixa de diálogo que se abre, clique em OK. Isso excluirá todos os registros visíveis e você verá apenas a linha do cabeçalho a partir de agora.
12. Clique na guia Dados e no ícone Filtro. Isso removerá o filtro e você poderá ver todos os registros restantes.

As etapas acima excluiriam cada terceira linha do conjunto de dados e você obteria os dados resultantes conforme mostrado abaixo.

Não se preocupe com os valores da coluna auxiliar nesta fase. Você pode excluir a coluna auxiliar agora.

A fórmula usada na Etapa 2 usa a função MOD - que dá o resto quando um número é dividido pelo outro. Aqui, usei a função ROW para obter o número da linha e ele é dividido por 3 (porque queremos excluir a cada terceira linha).

Como nosso conjunto de dados começa a partir da segunda linha (ou seja, a linha 2 contém o primeiro registro), uso a seguinte fórmula:= MOD (ROW (), 3) = 1

Estou igualando a 1 e para cada terceira linha, a fórmula MOD dará o restante como 1.

Você pode ajustar a fórmula de acordo. Por exemplo, se o primeiro registro do seu conjunto de dados começar da terceira linha em diante, a fórmula seria = MOD (ROW (), 3) = 2

Excluir automaticamente todas as outras linhas (ou enésima linha) usando VBA Macro (método rápido)

Se você tiver que excluir todas as outras linhas (ou a cada enésima linha) com frequência, poderá usar um código VBA e tê-lo disponível na Barra de Ferramentas de Acesso Rápido. Isso permitirá que você exclua rapidamente as linhas alternativas com um único clique.

Abaixo está o código que primeiro pede que você selecione o intervalo no qual deseja excluir linhas alternativas e, em seguida, exclua cada segunda linha no conjunto de dados selecionado.

Sub Delete_Every_Other_Row () Dim Rng As Range Set Rng = Application.InputBox ("Selecione o intervalo (excluindo cabeçalhos)", "Seleção de intervalo", digite: = 8) Para i = Rng.Rows.Count para 1 etapa -2 Se i Mod 2 = 0 Then Rng.Rows (i) .Delete End If Next i End Sub

Ao executar o código acima, primeiro ele solicitará que você selecione o intervalo de células. Depois de fazer a seleção, ele percorrerá cada linha e excluirá cada segunda linha.

Se você deseja excluir a cada terceira linha, você pode usar o código a seguir:

Sub Delete_Every_Third_Row () Dim Rng As Range Set Rng = Application.InputBox ("Selecione o intervalo (excluindo cabeçalhos)", "Seleção de intervalo", digite: = 8) Para i = Rng.Rows.Count para 1 etapa -3 Se i Mod 3 = 0 Then Rng.Rows (i) .Delete End If Next i End Sub

Onde colocar esse código de macro VBA?

Você precisa colocar este código em um módulo regular no editor VB no Excel.

Abaixo estão as etapas para abrir o Editor Vb, adicionar um módulo e colocar o código nele:

1. Copie o código VBA acima.
2. Vá para a guia Desenvolvedor.
3. Clique em Visual Basic.
4. No Editor VB, clique com o botão direito em qualquer um dos objetos da pasta de trabalho.
5. Vá para Inserir e clique em Módulo.
6. No módulo, cole o código VBA acima.
7. Feche o Editor VB.

Depois de ter o código no Editor VB, você pode usar os seguintes métodos para executar o código:

• Execute uma macro a partir do Editor VB (clicando no botão play verde na barra de ferramentas do Editor VB)
• Colocando o cursor em qualquer linha do código e usando a tecla F5
• Ao atribuir a macro a um botão / forma
• Adicionando a macro à barra de ferramentas de acesso rápido

Aqui está um tutorial detalhado sobre como executar uma macro no Excel.

Se você tiver que fazer isso com freqüência, considere adicionar o código VBA à Pasta de trabalho pessoal de macros. Dessa forma, ele sempre estará disponível para uso em todas as pastas de trabalho.

Quando você tem um código VBA em uma pasta de trabalho, precisa salvá-lo como um campo habilitado para macro (com a extensão .XLSM)

Observação: como todas as alterações feitas pelo código VBA são irreversíveis, é melhor primeiro fazer uma cópia de backup da pasta de trabalho / planilha e, em seguida, executar este código.

Excluir todas as outras colunas (ou cada enésima coluna)

Excluir cada linha alternativa ou cada terceira / quarta linha é mais fácil, pois você pode usar a opção de filtro. Tudo que você precisa fazer é usar uma fórmula que identifica linhas alternativas (ou a cada terceira / quarta linha) e filtra essas linhas.

Infelizmente, essa mesma metodologia não funcionará com colunas - já que você não pode filtrar colunas da maneira como filtra linhas.

Portanto, se você tiver que excluir todas as outras colunas (ou cada terceira / quarta / enésima coluna), será necessário ser um pouco mais criativo.

Nesta seção, mostrarei dois métodos que você pode usar para excluir todas as outras colunas do Excel (e você pode usar o mesmo método para excluir cada terceira / quarta / enésima coluna, se desejar).

Excluir colunas alternativas usando fórmulas e método de classificação

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja excluir todas as outras colunas (excluindo a coluna de cabeçalho A)

O truque aqui seria identificar colunas alternativas usando uma fórmula e, em seguida, classificar as colunas com base nessa fórmula. Depois de ter as colunas classificadas juntas, você pode selecionar e excluir manualmente.

Abaixo estão as etapas para excluir todas as outras colunas no Excel:

1. Insira uma linha acima da linha do cabeçalho (clique com o botão direito em qualquer célula do cabeçalho, clique em Inserir e clique na opção Linha inteira)
2. Insira a seguinte fórmula na célula acima da coluna mais à esquerda no conjunto de dados e copie para todas as células: = MOD (COLUNA (), 2)
3. Converta os valores da fórmula em números. Para fazer isso, copie as células, clique com o botão direito e vá em Colar Especial -> Colar valores.
4. Selecione o conjunto de dados inteiro (excluindo quaisquer cabeçalhos na coluna). Neste exemplo, selecionei B1: G14 (e NÃO coluna A)
5. Clique na guia Dados
6. Clique no ícone Classificar
7. Na caixa de diálogo Classificar, faça as seguintes alterações:
   • Clique no botão ‘Opções’ e na caixa de diálogo que se abre, clique em ‘Classificar da esquerda para a direita’ e, em seguida, clique em Ok
   • Na lista suspensa Classificar por, selecione Linha 1. Esta é a linha que possui os resultados da fórmula
   • No menu suspenso Pedido, selecione do menor para o maior.
8. Clique OK

As etapas acima classificam todas as colunas e reúnem todas as colunas alternativas em um lugar (no final).

Agora você pode selecionar todas essas colunas (para as quais o valor da fórmula é 1) e excluí-las.

Embora esta não seja a melhor solução, ela ainda dá conta do recado.

No caso de desejar excluir a cada terceira ou quarta coluna, você precisará ajustar a fórmula de acordo.

Tutorial relacionado: Como classificar no Excel (por linhas, colunas, cores, datas e números)

Excluir colunas alternativas usando VBA

Outra maneira rápida de excluir colunas alternativas é usar o código VBA abaixo:

Sub Delete_Every_Other_Column () Dim Rng As Range Set Rng = Application.InputBox ("Selecione o intervalo (excluindo cabeçalhos)", "Seleção de intervalo", digite: = 8) Para i = Rng.Columns.Count para 1 Etapa -2 Se i Mod 2 = 0 Then Rng.Columns (i) .Delete End If Next i End Sub

O código acima pede que você selecione um intervalo de células que contém as colunas. Aqui você precisa selecionar as colunas, exceto aquela que contém o cabeçalho.

Depois de especificar o intervalo de células com os dados, ele usará um For Loop e excluirá todas as outras colunas.

Caso queira excluir a cada terceira coluna, você pode usar o código abaixo (e ajustar de acordo para excluir a enésima coluna)

Sub Delete_Every_Third_Column () Dim Rng As Range Set Rng = Application.InputBox ("Selecione o intervalo (excluindo cabeçalhos)", "Seleção de intervalo", digite: = 8) Para i = Rng.Columns.Count para 1 Etapa -3 Se i Mod 3 = 0 Then Rng.Columns (i) .Delete End If Next i End Sub

As etapas sobre onde colocar este código VBA e como usá-lo abordadas na seção acima intitulada - “Excluir automaticamente todas as outras linhas (ou enésima linha) usando a macro VBA (método rápido)”

Espero que você tenha achado este tutorial do Excel útil.