How to move entire row to the bottom of active sheet based on cell value in Excel?
In Excel, you may often need to reorganize your data based on the status or value in a particular column. For example, after tracking task progress, you might want all rows where the status is "Done" to be moved to the bottom of your worksheet, keeping active or in-progress tasks at the top. Automatically relocating entire rows according to a cell value can make your worksheet easier to manage, highlight priorities, and help you focus efficiently on unfinished items.
There are several ways to achieve this in Excel, including using VBA code for automation or leveraging formulas and Excel's built-in sorting functionality. Each approach has its own strengths and is suitable for different scenarios. Below are step-by-step solutions to help you move entire rows to the bottom of your active worksheet based on a specific cell value.
- Move entire row to the bottom of active sheet based on cell value with VBA code
- Move entire row to the bottom using Excel formulas and sorting
Move entire row to the bottom of active sheet based on cell value with VBA code
Suppose you have a table where column C contains a status, such as "Done", and you would like any row with "Done" in column C to be instantly relocated to the end of your data range. This VBA solution is especially useful if you want a repeatable, semi-automated process that handles dynamic data changes without manual sorting.
1. Press Alt + F11 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 SubNote: In this VBA code, the specific value being referenced is “Done”. You can modify this value in the code to match the value you want to trigger the row movement (for example, you may change it to "Completed" or any other status that fits your workflow). Ensure the value matches exactly, including case and spacing, or consider adjusting the code for case-insensitive matching if required.
3. Press the F5 key or click the Run button to execute the code. In the popping up Kutools for Excel dialog box, select the column range where the target value may appear, then click the OK button.
After confirming, the code will automatically search for rows with the value “Done” in your selected column and relocate them to the bottom of your data range. This rearrangement happens instantly, saving you the time of manually sorting or dragging rows.
Tips:
- If you have headers, make sure not to include them in your selection when prompted, to avoid moving the header row.
- This VBA script only applies to the currently active worksheet. If you need to perform this operation on multiple sheets, repeat the process for each sheet.
- If your data contains formulas or linked cells, moving rows may cause references to shift. Double-check your worksheet after running the macro to ensure data integrity.
This VBA method excels in situations where you need to process a large number of rows regularly, particularly when manual sorting is inefficient. However, if you require a solution that does not involve macros, or if you prefer to work directly with formulas and Excel’s standard tools, consider the next approach.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Move entire row to the bottom using Excel formulas and sorting
For users who prefer not to use macros or want a more transparent, formula-based way to manage their data, you can achieve the same effect by using helper columns and Excel’s built-in sorting functionality. This method works well in collaborative environments or files that need to be shared with others who may have macro security settings enabled.
1. Insert a new helper column to the right or left of your existing data. For illustration, if your data headers are in row1 and values start from row 2, and your status is in column C, insert a new column D and label it "SortKey" or similar.
2. In the first cell of the helper column (excluding header, e.g., D2), enter the following formula:
=IF(C2="Done",1,0)This formula will assign 1 to rows where column C is "Done", and 0 to all other rows.
3. Press Enter to confirm the formula, then copy it down alongside all your data rows. Simply drag the fill handle from D2 down to the last row of your data, or double-click the fill handle for automatic filling if the adjacent column is populated.
4. Select any one cell in your data range, then from the Excel ribbon, go to the Data tab and click Sort.
5. In the Sort dialog box, select the helper column ("SortKey") in the "Sort by" dropdown, and choose Smallest to Largest order. This will keep all rows with "Done" (marked 1) at the bottom, and the rest (marked 0) at the top.
6. Click OK to apply the sort. Your data will now be rearranged so that all completed or "Done" entries appear at the bottom of the table.
Parameter explanations and tips:
- You can change "Done" in the formula to any other status indicator relevant to your sheet (e.g., "Completed", "Inactive"). Make sure the spelling matches your data exactly.
- If you want to move rows with multiple values (e.g., both "Done" and "Canceled"), use a formula like:
=IF(OR(C2="Done",C2="Canceled"),1,0) - To reverse the effect (i.e., "Done" rows at the top), sort in Largest to Smallest order or swap 0 and 1 in the formula.
This approach does not delete or hide any rows and keeps your data structure intact. It is ideal for collaborating with others, ensuring compatibility across different Excel versions, and avoiding potential issues with macro-enabled files.
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in