Note: The other languages of the website are Google-translated. Back to English

How to move entire row to the bottom of active sheet based on cell value in Excel?

For moving entire row to the bottom of active sheet based on cell value in Excel, please try the VBA code in this article.

Move entire row to the bottom of active sheet based on cell value with VBA code


Move entire row to the bottom of active sheet based on cell value with VBA code


For example, as below screenshot shown, if a cell in column C contains a certain value “Done”, then move the entire row to the bottom of current sheet. Please do as follows.

1. Press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the window.

VBA code: Move entire row to bottom of active sheet based on cell value

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: In the VBA code, “Done” is the cell value you will move entire row based on. You can change it as you need.

3. Press the F5 key to run the code, then in the popping up Kutools for Excel dialog box, select the column range which the certain value exists in, then click the OK button.

After clicking the OK button, the entire row which contains the value “Done” in the specified column is moved to the bottom of the data range automatically.


Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How could I make it so the kutools selects specific rows without user input?
This comment was minimized by the moderator on the site
Hi Anon,
I am sorry, I'm not sure what you mean. Would be nice if you can explain it again or provide a screenshot to show what you are trying to do .
Thanks for your comment.
This comment was minimized by the moderator on the site
Hi Crystal, this code works great, thank you. I would like move all the rows which contain word "Complete" in column D to the top of the table (insert in row 3). Is that possible? then I would like to delete all these complete rows which contain "yesterday date" in column V.
This comment was minimized by the moderator on the site
Hello. This is almost perfect for what I want. I have part of the same request as Anon had to make this work without user input along with some extras.

I am wondering if it is possible to have the only column searched to be i4 to i50 and have it automatically run at open or anytime the i column is edited. Also if it would be possible to move the rows to the bottom of the sheet without any blank rows between "done" rows and "no" rows. Currently if I select i4:i50 and if I only have data up to row 25 it will paste the "done" rows ascending from row 50 instead of row 25. The number of rows in my sheet is constantly changing and shouldn't get to more than 50. Thanks for the help.
This comment was minimized by the moderator on the site
Hi Brandon,
Sorry can help you with that. Thank you for your comment.
This comment was minimized by the moderator on the site
Hello, is there a way to tweak this so that it moves a row somewhere else in the same sheet besides the end? I have a sheet that has order information for dated orders and upcoming orders yet to be dated and I have it so that when I put an "X" in column A things highlight and bold depending on part #'s and shipping locations. Now I have to physically cut and paste the newly dated (shipping date) so that it fits in at the top sorted by date (1st thru end of month). I have been able to conditional format everything up to this point, but I don't think I can move rows that way. I was wondering if VBA could do this, move a row when a date is entered to fit in with the other dated rows?
This comment was minimized by the moderator on the site
Hello, the only way I can get this to work is if I "run" the macro in the Visual Basic sub screen. Is it possible to have this VBA code run automatically, once the user types in "Done"? Each time I'm getting the Kutools for Excel dialog box pop up to ask the parameters I'm requesting the code to search by. I have sort of worked around this by replacing : xTxt = ActiveSheet.UsedRange.AddressLocal with the parameters I need searched and hitting enter. But it would be more convenient if it automatically made the changes after "Done" was entered. Thank you!
This comment was minimized by the moderator on the site
Hi Anon,
The below VBA code can do you a favor. Please have a try.
Please right click the sheet tab (the sheet contains the data you will move to bottom), select View Code from the context menu and copy the below code into the Code window.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20190925
Dim xRg As Range
Dim xIRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xEndRow As Long
Dim I As Long
Dim xDStr As String
On Error Resume Next
xDStr = "C:C"
Set xRg = Me.Range(xDStr)
Set xIRg = Application.Intersect(Target, xRg)
If xIRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False

If Target = "Done" Then
xEndRow = ActiveSheet.UsedRange.Rows.Count + 1
Target.EntireRow.Cut
Rows(xEndRow).Insert Shift:=xlDown
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hi crystal,

Thank you for posting this code. I would like to know how to get the code to move the row back to the top if Done was typed in error. Could a secondary code be added for "move" to move it to the top, and "done" for the bottom?
This comment was minimized by the moderator on the site
hello crystal the code u gave to anon to have the vba run code automatically is great but i cant insert a sheet of rows is there a possible way to fix it
This comment was minimized by the moderator on the site
Hi sarah,
Sorry for the inconvenience. Please try the below VBA. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

'Updated by Extendoffice 20200424

Dim xRg As Range

Dim xIRg As Range

Dim xTxt As String

Dim xCell As Range

Dim xEndRow As Long

Dim I As Long

Dim xDStr As String

On Error GoTo Err1

xDStr = "C:C"

Set xRg = Me.Range(xDStr)

Set xIRg = Application.Intersect(Target, xRg)

If xIRg Is Nothing Then Exit Sub

Application.ScreenUpdating = False

Application.EnableEvents = False



If Target.Value = "Done" Then

'xEndRow = ActiveSheet.UsedRange.Rows.Count + 1

xEndRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1

Target.EntireRow.Cut

Rows(xEndRow).Insert Shift:=xlDown

End If

Err1:

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub
This comment was minimized by the moderator on the site
I tried to copy this code but it continues to say invalid use of Me.
This comment was minimized by the moderator on the site
What if you only need to move rows under Column A and B; then Column C should retain? should we still use EntireRow?
This comment was minimized by the moderator on the site
Hi Jerel,
Try the below code. Hope I can help.

Sub MoveToEnd()

'Updated by Extendoffice 20200717

Dim xRg As Range

Dim xTxt As String

Dim xCell As Range

Dim xEndRow As Long

Dim xIntR As Integer

Dim I As Long

Dim xWs As Worksheet

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

xWs = xRg.Worksheet

xWs.Activate

Application.ScreenUpdating = False

For I = xRg.Rows.Count To 1 Step -1

If xRg.Item(I) = "Done" Then

Rows(xEndRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

xIntR = xRg.Cells(I).Row

Range("A" & xIntR & ":B" & xIntR).Select

Selection.Cut

Range("A" & xEndRow).Select

ActiveSheet.Paste

xEndRow = xEndRow + 1



End If

Next

Application.ScreenUpdating = True

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



I am having issues using the code provided and keep receiving a syntax error. I am super new to excel and have been trying to self teach what I need to run my home business. I have an inventory spreadsheet id like to be able to designate items in a column as RETIRED? yes/no and if yes, they move to the bottom of the sheet, in alphabetical order, without leaving a blank space in the main spread sheet. we have items retire completely and then come back for a special re-release in limited quantities and id like these items stored at the bottom of my sheet until they become available again. Thank you.
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.
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
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
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! 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 sales@extendoffice.com 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
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
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations