Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

 Como preencher automaticamente a fórmula ao inserir linhas no Excel?

Ao inserir linhas em branco entre as linhas de dados existentes, a fórmula não será copiada e colada na nova linha, você deve arrastar ou preenchê-la manualmente. Mas, às vezes, você gostaria de preencher a fórmula ao inserir linhas em branco entre intervalos de dados automaticamente, conforme a captura de tela mostrada a seguir, neste artigo, falarei sobre alguns truques rápidos para resolver essa tarefa.

doc fórmula de preenchimento automático inserindo linha 1

Fórmula de preenchimento automático ao inserir linhas em branco ao criar uma tabela

Fórmula de preenchimento automático ao inserir linhas em branco com código VBA


Fórmula de preenchimento automático ao inserir linhas em branco ao criar uma tabela


No Excel, o formato da tabela pode ajudá-lo a preencher a fórmula acima nas novas linhas em branco inseridas automaticamente, faça o seguinte:

1. Selecione o intervalo de dados que deseja preencher automaticamente a fórmula e clique em inserção > mesa, veja a captura de tela:

doc fórmula de preenchimento automático inserindo linha 2

2. No Criar a tabela caixa de diálogo, verifique o Minha tabela tem cabeçalhos se houver cabeçalhos em seus dados, consulte a captura de tela:

doc fórmula de preenchimento automático inserindo linha 3

3. Então clique OK botão, e a tabela foi criada, e agora, quando você inserir uma linha em branco, a fórmula acima será inserida na nova linha automaticamente, veja a imagem:

doc fórmula de preenchimento automático inserindo linha 4


Fórmula de preenchimento automático ao inserir linhas em branco com código VBA

Se você não gosta de alterar os dados para a tabela, o código VBA abaixo também pode ajudá-lo a resolver esse problema.

1. Selecione a guia da planilha que contém as fórmulas que deseja preencher automaticamente e clique com o botão direito para escolher Ver código do menu de contexto para ir para o Microsoft Visual Basic para Aplicações janela e, em seguida, copie e cole o seguinte código no módulo:

Código VBA: fórmula de preenchimento automático ao inserir linhas em branco

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

doc fórmula de preenchimento automático inserindo linha 5

2. Em seguida, salve e feche este código, volte para a planilha, agora, ao clicar duas vezes em uma célula entre os dados, uma nova linha será inserida abaixo da célula ativa e a fórmula também será preenchida automaticamente.


Demonstração: Preenchimento automático da fórmula ao inserir linhas em branco


As melhores ferramentas de produtividade para escritório

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

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de 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 (salvar e aplicar 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 ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em 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!
officetab bottom
Say something here...
symbols left.
You are guest
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.
    Marcelo Moreira · 2 years ago
    Excellent simple piece of code!!!!
  • To post as a guest, your comment is unpublished.
    Niket Shet · 2 years ago
    Hi,

    This code is very helpful, however, I have a slightly different requirement. The formulas should be copied when the row is inserted manually, i.e. by "insert row".
    Can you please resend the code after incorporating this change i.e. instead of "Worksheet_BeforeDoubleClick", some other trigger point to be used "insert row/etc"

    Note - I do have any exposure to coding being from the Accounts background.


    Regards
    Niket Shet
  • To post as a guest, your comment is unpublished.
    David Banks · 2 years ago
    I am using the code to insert a line on double-click. However, there is something wrong, Excel does not increment the first reference in my formula in the next but one row. However, it is corrected for all remaining rows.
    I inserted a line at row 144 and this is what I got: (note S144 in 3rd line should be S145)
    row 144 =(S143+D144+E144)-(K144+L144+M144+N144+O144+Q144+R144)
    row 145 =(S144+D145+E145)-(K145+L145+M145+N145+O145+Q145+R145)
    row 146 =(S144+D146+E146)-(K146+L146+M146+N146+O146+Q146+R146)
    row 147 =(S146+D147+E147)-(K147+L147+M147+N147+O147+Q147+R147)
    Any clues as to why it does this, I don't think it is the VBA???
    David
  • To post as a guest, your comment is unpublished.
    gt · 4 years ago
    Does the table method work in excel for xp (2002) and if yes, how?
  • To post as a guest, your comment is unpublished.
    RR · 4 years ago
    The code works very well, but does not update cell reference in first row after the inserted row.See below"
    e.g
    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    RR · 4 years ago
    This code is very good, but it does not update the cell reference in the first following row after the inserted row. Can you please help?
    e.g:

    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 4 years ago
    Above formula is very helpful for me please tell me how to use this code permanently
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 4 years ago
    dear above formula which helps to update the above formula on inserting row by double clicking. I want to know how to use this code permanently because when i close the sheet and reopen code finishes. Please help to solve the issue. This formula is very helpful for me
    • To post as a guest, your comment is unpublished.
      David Hayes · 3 years ago
      Very easy solution. Thank you very much.
  • To post as a guest, your comment is unpublished.
    Bhavin · 5 years ago
    I followed the VBA code mentioned above for my xls. I have one .xls with multiple sheets in it , i want if i insert a row in my one sheet at the start or any of the sheet for that matter it should auto insert row in all my sheet in the same worksheet. Also each of my sheet as formulas hence i want the moment auto insert takes place the formula of above row should get autofill in the new row which is not happening totally if i follow the VBA code mentioned , the rows are getting inserted in all my sheets the formula also shows up but it misses one row in it.
    Eg. I have sheet A with 100 rows , sheet B with 100 ros and so on with other sheets. If I insert row in sheet A it shows auto insert in Sheet B , but sheet B values of some column are linked with sheet A and the moment i insert a row eg 10 , row 11 is a new row and the same row 11 in sheet B also shows up but the value it should pull from row 11 of sheet A from column C rather it shows row 12 of sheet A , this happens on any row i insert the value in sheet B is always of the next row rather than same row that i inserted. Another problem that comes here is with my first column Sr. no the moment i insert a row in 5 , sr no 6 with new row shows up but prior to that i already had row 6 as i have a formula for Sr. no A6+1 as my column starts from A6 and so on so how to sort this issue with 2 rows with same sr. no. This is very crucial sheet am working on and i need to get this sorted, i have refereed many VBA codes but none is effective. Hoping for a proper solution to my issue.