Ir para o conteúdo principal

Como mover uma linha inteira para a parte inferior da planilha ativa com base no valor da célula no Excel?

Para mover a linha inteira para a parte inferior da planilha ativa com base no valor da célula no Excel, tente o código VBA neste artigo.

Mova a linha inteira para a parte inferior da planilha ativa com base no valor da célula com o código VBA


Mova a linha inteira para a parte inferior da planilha ativa com base no valor da célula com o código VBA

Por exemplo, como mostrado na captura de tela abaixo, se uma célula na coluna C contiver um determinado valor “Concluído”, mova a linha inteira para o final da planilha atual. Faça o seguinte.

1. Pressione outro+ F11 simultaneamente para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Módulo. Em seguida, copie e cole o código VBA abaixo na janela.

Código VBA: mova a linha inteira para o final da planilha ativa com base no valor da célula

Sub MoveToEnd()
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xEndRow As Long
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set xRg = Application.InputBox("Select range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
        MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
        GoTo lOne
    End If
    xEndRow = xRg.Rows.Count + xRg.Row
    Application.ScreenUpdating = False
    For I = xRg.Rows.Count To 1 Step -1
        If xRg.Cells(I) = "Done" Then
           xRg.Cells(I).EntireRow.Cut
           Rows(xEndRow).Insert Shift:=xlDown
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Note: No código VBA, “Pronto ”É o valor da célula em que você moverá a linha inteira. Você pode alterá-lo conforme necessário.

3. aperte o F5 chave para executar o código, em seguida, no pop-up Kutools for Excel caixa de diálogo, selecione o intervalo da coluna em que o determinado valor existe e clique no botão OK botão.

Depois de clicar no OK botão, toda a linha que contém o valor “Concluído” na coluna especificada é movida para a parte inferior do intervalo de dados automaticamente.


Artigos relacionados:

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab 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!
Comments (30)
Rated 4.75 out of 5 · 2 ratings
This comment was minimized by the moderator on the site
How can you automatically move the rows in an online excel sheet?
This comment was minimized by the moderator on the site
Is there any way I can do this in an online excel sheet?
This comment was minimized by the moderator on the site
Is there a way to move rows back to the original place when the status of the input changes? For example if someone changes it from "Done" to "incomplete", is there a way to program excel to move it back?
Also, is this change permanent ? I've noticed after working the first time, it stopped working after that.

Thank you for your help and post!
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Hi zoe,

Thank you for your comment.
1. For the first question:
The row that was moved can't be moved back to its original place;
2. For the second question:
This VBA code needs to be run manually each time you need to move rows. If you want to move the row automatically when the cell value matches the condition, you can try the following VBA code.
Notes: You need to put this code in the Sheet (Code) editor (Right click the sheet tab and select View Code to open the editor). And change the column range B2:B12 to your own range.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updated Extendoffice 20230111
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xEndRow As Long
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set xRg = Range("B2:B12")
    If xRg Is Nothing Then Exit Sub
    If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
        MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
        GoTo lOne
    End If
    xEndRow = xRg.Rows.Count + xRg.Row
    Application.ScreenUpdating = False
    For I = xRg.Rows.Count To 1 Step -1
        If xRg.Cells(I) = "Done" Then
           xRg.Cells(I).EntireRow.Cut
           Rows(xEndRow).Insert Shift:=xlDown
        End If
    Next
    Application.ScreenUpdating = True

End Sub
This comment was minimized by the moderator on the site
Hi Crystal,

Thank you for all of the great codes. Is there a way to do this without the Kutools? I also don't see a dialog box to select a cell range, it isn't popping up for me.

Thank you,
Jaz
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi Jaz,
You can specify the cell range directly in the code without having to pop up the Kutools dialog box to select the range.
In the code, please replace the following line:
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", xTxt, , , , , 8)

with:
Set xRg = Range("C2:C13")
This comment was minimized by the moderator on the site
me again! I figured the code out. I had manually copied and pasted the code rather than use the copy button on the top right of the screen. I changed "done" to "x". I have run the code in each of the worksheets. As I start entering in "x" in those cells within the selected cell ranges from the dialog box, nothing happens (rows do not automatically move down to the bottom). I am VERY NEW to this....thanks for your help!
This comment was minimized by the moderator on the site
Hi,
If you want to automatically move the row to bottom when entering the specified word, please try the following VBA code.
Note: you need to enter the code into the Worksheet code window (right click the sheet tab and select View Code from the context menu).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updated by Extendoffice 20220520
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xEndRow As Long
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set xRg = Range("C2:C18")
    If xRg Is Nothing Then Exit Sub
    If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
        MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
        GoTo lOne
    End If
    xEndRow = xRg.Rows.Count + xRg.Row
    Application.ScreenUpdating = False
    For I = xRg.Rows.Count To 1 Step -1
        If xRg.Cells(I) = "Done" Then
           xRg.Cells(I).EntireRow.Cut
           Rows(xEndRow).Insert Shift:=xlDown
        End If
    Next
    Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hi! I just purchased kutools so that I could *** this vba code to my excel sheet, as it will be an excellent feature to use! The instructions above are simple and helpful; however, once I get to the step where the code is copied and pasted in the window and press F5, it sent me to a box to name and create the macro. I did that but now it won't take me to the dialog box so I can select cell range. A compile error of "invalid outside procedure" pops up. Please help!
This comment was minimized by the moderator on the site
Hi,
I'm sorry to have misled you. The code can be used on its own and does not require the purchase of Kutools. If you don't need it, please email to to ask for a refund.
For the code to work smoothly, you need to make sure that your cursor is in the code window (click on any word in the code), then press the F5 key to run the code. Then the dialog box to select a cell range will pop up.
Sorry again for the inconvenience.
This comment was minimized by the moderator on the site
I have a list with check boxes that when one column is checked I need it to go to one section of the spreadsheet and if the other is checked instead it goes to the end. I have tried a hundred diff ways to do this can anyone help with this?
This comment was minimized by the moderator on the site
Hi Crystal,
Thanks for you help, the code works great but rather than move the row to the bottom of a page how to I move it to another tab i.e. a "Closed" tab?
This comment was minimized by the moderator on the site
Hi,
Sorry for replying so late.
In the code, you just need to change the line "If xRg.Cells(I) = "Done" Then" to If xRg.Cells(I) Like "*Done*" Then to get it done.
This comment was minimized by the moderator on the site
How do it if "Done" is only a part of a column string. Suppose my Columns contains value like - XYZDone, ABCDone, 123Done etc, can I just filter out based on partial string "Done"?
This comment was minimized by the moderator on the site
Hello, I have a task my boss has given me. It seemed simple enough at first but now I am confused as how to proceed. We have a Forecast sheet of the possible jobs and they have a "Order Probability" column by %. He wants me to set up 3 different sheets with 100-70%, 69%-41, and 40-0%. The idea is that as the information is typed inot the master sheet, when the percentage is entered in, it automatically gets copied into the proceeding sheet matching that percentile rage. I did this with a simple IF(and formula. However i need to sort inorder to loose the empty cells and make it look cleaner. Then when i sort , if i add a new Oder Probability offer to the master sheet, it does not automatically show it, without unsorting then sorting again. I apologize if this question does not belong her. But is there a string of code i could put in that would handle this issue easier? The only value that determines if the whole row gets moved is the K column. seems simple, but however complex for this excel beginner. Thanks in advance for your help.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations