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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agohello 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
- To post as a guest, your comment is unpublished.· 8 months agoHello, 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!
- To post as a guest, your comment is unpublished.· 6 months agoHi 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
Application.EnableEvents = True
Application.ScreenUpdating = True
- To post as a guest, your comment is unpublished.· 3 months agoHi 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?
- To post as a guest, your comment is unpublished.· 10 months agoHello, 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?
- To post as a guest, your comment is unpublished.· 1 years agoHello. 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.
- To post as a guest, your comment is unpublished.· 1 years agoHi Brandon,
Sorry can help you with that. Thank you for your comment.
- To post as a guest, your comment is unpublished.· 1 years agoHi 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.
- To post as a guest, your comment is unpublished.· 1 years agoHow could I make it so the kutools selects specific rows without user input?
- To post as a guest, your comment is unpublished.· 1 years agoHi 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.