Dica: outros idiomas são traduzidos pelo Google. Você pode visitar o English versão deste link.
Entrar
x
or
x
x
Registre-se
x

or

Como inserir linhas em branco quando o valor muda no Excel?

Supondo que você tenha uma variedade de dados, e agora você deseja inserir linhas em branco entre os dados quando o valor muda, para que você possa separar os mesmos valores seqüenciais em uma coluna como as seguintes capturas de tela mostradas. Neste artigo, vou falar sobre alguns truques para você resolver esse problema.

Insira linhas em branco quando o valor muda com a função Subtotal

Insira linhas em branco quando o valor muda com as colunas auxiliares

Insira linhas em branco quando o valor muda com o código VBA

Inserir um número específico de linhas em branco quando o valor é alterado com um recurso poderoso


Inserir quebra de página, linhas em branco, borda inferior ou cor de preenchimento quando o valor muda rapidamente

Se você tem Kutools for Excel's Distinguir diferenças recurso, você pode inserir rapidamente quebra de página, linhas em branco, borda inferior ou cor de preenchimento quando o valor for alterado conforme a necessidade. Veja a demonstração abaixo. Clique para baixar o Kutools for Excel!


Insira linhas em branco quando o valor muda com a função Subtotal

Com o Subtotal , você pode inserir linhas em branco entre os dados quando o valor muda como as seguintes etapas:

1. Selecione o intervalo de dados que deseja usar.

2. Clique Dados > Subtotal para abrir o Subtotal caixa de diálogo, e no Subtotal diálogo, faça as seguintes opções:

1: Selecione o nome da coluna que deseja inserir linhas em branco com base no valor do valor em Em cada mudança na seção;
2: Escolha Contagem de Use a função lista suspensa;
3: Verifique o nome da coluna em que deseja inserir o subtotal no Adicionar subtotal a caixa de listagem

3. Então clique OK, as linhas do subtotal foram inseridas entre os diferentes produtos, e os símbolos do contorno são exibidos à esquerda da tabela, veja a captura de tela:

4. E, em seguida, clique no número 2 na parte superior do símbolo do esboço para apenas exibir as linhas do subtotal.

5. Em seguida, selecione os dados do intervalo de subtotal e pressione Alt +; teclas de atalho para selecionar apenas as linhas visíveis, veja a captura de tela:

6. Depois de selecionar as linhas visíveis somente, pressione Excluir chave no teclado e todas as linhas de subtotal foram removidas.

7. Em seguida, clique em qualquer outra célula e volte Dados > Desagrupar > Esboço claro para remover os contornos, veja a captura de tela:

8. Os símbolos de contorno foram limpos de uma só vez e você pode ver linhas em branco inseridas entre os dados quando o valor é alterado, veja a captura de tela:

9. Por fim, você pode excluir a coluna A conforme você precisa.


Insira linhas em branco quando o valor muda com as colunas auxiliares

Com as colunas auxiliares, você pode inserir a fórmula primeiro e depois aplicar a Localizar e substituir Funcione, por fim, insira as linhas em branco entre os valores de mudança. Faça o seguinte:

1. Em uma célula em branco C3, por favor insira esta fórmula = A3 = A2, e na célula D4 digite esta fórmula = A4 = A3, veja a captura de tela:

2. Em seguida, selecione C3: D4 e arraste o identificador de preenchimento até o intervalo que deseja aplicar fórmulas de teses, e você receberá Verdadeiro or Falso nas células, veja a captura de tela:

3. E, em seguida, pressione Ctrl + F chaves para abrir o Localizar e substituir diálogo, no diálogo surgido, entre FALSE no Encontre o que caixa de texto abaixo Encontrar guia e clique em Opções botão para expandir este diálogo e escolha valores de Olhar dentro lista suspensa, veja a captura de tela:

4. Clique Encontrar tudo botão e, em seguida, pressione Ctrl + A para selecionar todos os resultados da descoberta, todas as células FALSE foram selecionadas de uma vez, veja a captura de tela:

6. Feche o Localizar e substituir diálogo, próximo passo, você pode inserir linhas em branco clicando em Página inicial > inserção > Inserir linhas de folha, e as linhas em branco foram inseridas nos dados quando o valor muda com base na coluna A, veja as capturas de tela:

7. Finalmente, você pode excluir a coluna auxiliar C e D conforme necessário.


Insira linhas em branco quando o valor muda com o código VBA

Se você está cansado de usar os métodos acima, aqui está um código também pode ajudá-lo a inserir linhas em branco entre os valores alterados ao mesmo tempo.

1. Mantenha pressionado o ALT + F11 chaves para abrir o Janela Microsoft Visual Basic for Applications.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: Insira linhas em branco quando o valor muda

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Então aperte F5 chave para executar este código e uma caixa de prompt aparecerá para permitir que você selecione uma coluna de dados que deseja inserir linhas em branco quando o valor muda com base em você, veja a captura de tela:

4. E, em seguida, clique em OK, as linhas em branco foram inseridas entre dados quando o valor muda com base na coluna A.


Inserir um número específico de linhas em branco quando o valor é alterado com um recurso poderoso

Se você tentou com os métodos problemáticos acima, aqui, vou apresentar uma ferramenta útil, Kutools for Excel's Distinguir diferenças pode ajudá-lo a inserir quebra de página, linhas em branco, borda inferior ou cor de preenchimento quando o valor da célula mudar rápida e facilmente.

Dicas:Para aplicar isso Distinguir diferenças recurso, em primeiro lugar, você deve baixar o Kutools for Excele aplique o recurso de maneira rápida e fácil.

Depois de instalar Kutools for Excel, faça o seguinte:

1. Clique Kutools > Formato > Distinguir diferenças, veja a captura de tela:

2. No Distinguir diferenças por coluna-chave Caixa de diálogo, faça as seguintes operações:

  • Selecione o intervalo de dados que você deseja usar e, em seguida, escolha a coluna-chave na qual deseja inserir linhas em branco com base;
  • Então verifique Linha em branco opção do Opções seção e insira o número de linhas em branco que você deseja inserir.

3. Então clique Ok botão e o número específico de linhas em branco foram inseridos nos dados se o valor da célula mudar, consulte capturas de tela:

Clique para baixar o Kutools para Excel e teste gratuito agora!


Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Inserir rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de endereços e enviar e-mails ...
  • Bar Super Fórmula (facilmente editar várias linhas de texto e fórmula); Layout de leitura (leia e edite facilmente grandes números de células); Colar para intervalo filtrado...
  • Mesclar células / linhas / colunas sem perder dados; Conteúdo de células divididas; Combinar linhas / colunas duplicadas... Prevenir Células Duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Exclusivo Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros de trabalho; Seleção aleatória ...
  • Cópia exata Múltiplas Células sem alterar a referência da fórmula; Criar automaticamente referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair texto, Adicionar texto, remover por posição, Remover espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salve e aplique esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro especial por negrito, itálico ...
  • Combinar pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Dividir dados em várias planilhas; Lote Converter xls, xlsx e PDF...
  • Mais de recursos poderosos do 300. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos Avaliação gratuita de um dia de 30.
kte tab 201905

A guia Office traz a interface com guias para o Office e torna seu trabalho muito mais fácil

  • Ativar edição e leitura com guias no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
fundo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hassan · 11 months ago
    Hello,
    Is very usefull in case i need to insert 1 row, but if i need to insert 145 rows in every time the data change in spwcific column, how can i do it??
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi, Hassan,
      To insert multiple blank rows when value changes in a specific column, you should apply the following VBA code:

      Note: In the below code, you should change the number 99 to your need, for example, when you insert 145 blank rows, you should change the number 99 to 144. Please try it, hope it can help you!

      Sub InsertRowsAtValueChange()
      Dim Rng As Range
      Dim WorkRng As Range
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      Application.ScreenUpdating = False
      For i = WorkRng.Rows.Count To 2 Step -1
      If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
      Range(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        DCalk · 10 months ago
        Very helpful. The code that skyyang shows above worked perfectly. Just make sure that the data doesn't already have spaces in it.

        I don't understand VBA, but I believe if you wanted to add more rows underneath data that already had the spacing, there should be a way to ignore spaces.

        Could a line be added to ignore or skip over blank lines? That might make this code more universal and repeatable if needed. Also a delete function that is similar to this may be useful so undo isn't necessary.
  • To post as a guest, your comment is unpublished.
    Suzi · 1 years ago
    RE: insert blank rows when value changes with vba code
    Is there a way that I can save the Range & not have to pick it every time I run it?
  • To post as a guest, your comment is unpublished.
    Eric F · 1 years ago
    The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Steve · 1 years ago
    I've been using my own solution for some time.
    1. Insert a helper column into Column A
    2. In A2, type "if(B2=B1,A1,A1+1)"
    3. Copy that formula down to the last row
    4. Copy all the populated cells in column A and Paste Special (Values) over them
    5. Copy all the cells again and paste them into column A in the first unpopulated cell (e.g. if you have 104 rows of data plus a header row you would paste into cell A106)
    6. Click on Data and Remove Duplicates (only on the cells you just pasted in Step 5; not on all rows)
    7. Sort all of Column A
    8. Delete Column A

    Seems like a lot of steps but only takes a few seconds.
  • To post as a guest, your comment is unpublished.
    Madhur · 2 years ago
    [quote name="Hudson"]Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.[/quote]

    Please let me know how to insert more than 1 row.
  • To post as a guest, your comment is unpublished.
    Davis · 2 years ago
    Hi there,

    These are almost useful! The first method doesn't work for me because when I follow the steps explicitly, the the data that I delete in the subtotal panes deletes the entire columns that I've sorted.

    In the second method when I get to the step where I insert sheet rows, the rows are inserted ABOVE the FALSE cells which breaks up the data, but the last selection of every group is then added to the group below.

    Any advice???
  • To post as a guest, your comment is unpublished.
    Hudson · 3 years ago
    Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.
  • To post as a guest, your comment is unpublished.
    Rafael · 3 years ago
    Where in the code would I need to modify to include more than one row, I need to add 10 after each break...

    Thanks
  • To post as a guest, your comment is unpublished.
    Ivo Tamm · 3 years ago
    Thank you very much for describing multiple solutions for the problem.
    I used the Visual Basic one and changed
    [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] with
    [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlMedium
    End With[/quote]
    This will draw lines between rows with changing values instead of empty lines.
  • To post as a guest, your comment is unpublished.
    Saul · 4 years ago
    Thank you so much, looked everywhere and this was the only solution that worked for me that was also in plain english.

    All the other Excel tutorial websites didn't break it down step-by-step they way you did, thank you very much.

    cheers : )
  • To post as a guest, your comment is unpublished.
    Abner · 4 years ago
    I have a column with the numbers 5,4,3,2 and 1 (a LOT of them) and I wanna separate the Fives from the others, like:

    5
    5
    5

    4
    3
    2
    1

    5
    5
    5
    5
    5

    4
    3
    2
    1

    The numbers are in desc order and there are always a four below a five,
    so I tried:

    Sub InsertRowsAtValueChange()
    'Update 20140716
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value = 5 And WorkRng.Cells(i - 1, 1).Value = 4 Then
    WorkRng.Cells(i, 1).EntireRow.Insert
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    But it didn't work. Can you help me?