Move Row to Bottom in Excel If Cell Contains a Value

If you are searching for the solution or some special tricks to move row to bottom if a cell contains a specific value in Excel then you have landed in the right place. There are some quick steps to move row to bottom if the cell contains a specific value in Excel. This article will show you every step with proper illustrations so you can easily apply them for your purpose. Let’s get into the central part of the article.

How to Move Row to Bottom If Cell Contains a Value: 2 Examples

Suppose, you have a dataset that contains the customer name, product brand name and sales amount, and the target status. Some cells of the target column are filled with text “ Passed” in the rows which meet the target amount. And, you want to move rows to the bottom in which Target column cells are filled with “Passed” text.

In this section, I will show you the quick and easy steps to move from row to bottom if the cell contains a specific value in Excel on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If anything in this article doesn’t work in your version then leave us a comment.

1. Move Row to Bottom If Cell Contains a Specific Text

You have to use a VBA code to move row to bottom in which cells contain a specific text. Follow the below steps to do this.

Steps:

First, go to the top ribbon and press on the Developer then press on the Visual Basic option from the menu.

You can use ALT + F11 to open the Microsoft Visual Basic for Applications window if you don’t have the Developer tab added.

Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.

Now, a new “Module” window will appear. And Paste this VBA code into the box.

Sub Move_Row_To_End()
Dim xR As Range
Dim xT As String
Dim xC As Range
Dim xER As Long
Dim p As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xT = ActiveWindow.RangeSelection.AddressLocal
Else
xT = ActiveSheet.UsedRange.AddressLocal
End If
lOne:
Set xR = Application.InputBox("Select the Input range:", "ExcelDemy", xT, , , , , 8)
If xR Is Nothing Then Exit Sub
If xR.Columns.Count > 1 Or xR.Areas.Count > 1 Then
MsgBox " Selected Multiple Columns ", vbInformation, "Exceldemy"
GoTo lOne
End If
xER = xR.Rows.Count + xR.Row
Application.ScreenUpdating = False
For p = xR.Rows.Count To 1 Step -1
If xR.Cells(p) = "Passed" Then
xR.Cells(p).EntireRow.Cut
Rows(xER).Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

🔎 VBA Code Breakdown:

♣ Segment 1:

Sub Move_Row_To_End()
Dim xR As Range
Dim xT As String
Dim xC As Range
Dim xER As Long
Dim p As Long
On Error Resume Next

Here, there is created a new sub named Move_Row_To_End. Then called 4 variables and commanded to go to the next line if found any error.

♣ Segment 2:

If ActiveWindow.RangeSelection.Count > 1 Then
xT = ActiveWindow.RangeSelection.AddressLocal
Else
xT = ActiveSheet.UsedRange.AddressLocal
End If

Before running the code, if the number of selected cells is greater than 1 then the selected range will be the input range of the code. Else it will select all of the used cells as the input range.

♣ Segment 3:

lOne:
Set xR = Application.InputBox("Select the Input range:", "ExcelDemy", xT, , , , , 8)

This line creates an input box named “Exceldemy” which will take the input of the cell range.

♣ Segment 4:

If xR.Columns.Count > 1 Or xR.Areas.Count > 1 Then
MsgBox " Selected Multiple Columns ", vbInformation, "Exceldemy"
GoTo lOne
End If

These lines denote that if you select more than one column then it will give a message box saying “Selected Multiple Columns”.

♣ Segment 5:

xER = xR.Rows.Count + xR.Row
Application.ScreenUpdating = False
For p = xR.Rows.Count To 1 Step -1
If xR.Cells(p) = "Passed" Then
xR.Cells(p).EntireRow.Cut
Rows(xER).Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

This is the final part of the code. Here, For loop works to select the rows containing the given cell value “Passed” then cut the row and paste it to the bottom. After that, it will end the sub.

To run the code go to the top menu, press on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.

Then, after running the code, there will open a pop-up window. Here, you will have to select the input range. For this dataset, the input range is E5:E20. Then Press OK.

As a result, you will see that the rows containing the specific text “Passed” have gone to the bottom of the dataset.

2. Move Row to Bottom If a Cell Contains Greater Than a Number

When you want to move rows to the bottom that meet specific criteria, you have to change the previous slightly. Suppose, for the same dataset, you want to move rows that contain sales values of more than $4,000,000. Follow the steps below for this.

Steps:

First. create the same dataset in a new worksheet. Then, create a new module to insert the modified code using the same steps mentioned before.

Now, a new “Module” window will appear. And Paste this VBA code into the box.

Sub Move_Row_To_End()
Dim xR As Range
Dim xT As String
Dim xC As Range
Dim xER As Long
Dim p As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xT = ActiveWindow.RangeSelection.AddressLocal
Else
xT = ActiveSheet.UsedRange.AddressLocal
End If
lOne:
Set xR = Application.InputBox("Select the Input range:", "ExcelDemy", xT, , , , , 8)
If xR Is Nothing Then Exit Sub
If xR.Columns.Count > 1 Or xR.Areas.Count > 1 Then
MsgBox " Selected Multiple Columns ", vbInformation, "Exceldemy"
GoTo lOne
End If
xER = xR.Rows.Count + xR.Row
Application.ScreenUpdating = False
For p = xR.Rows.Count To 1 Step -1
If xR.Cells(p) > 4000000 Then
xR.Cells(p).EntireRow.Cut
Rows(xER).Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

Then, run the code using the Run option,
After running the code, there will open a pop-up window. Here, you will have to select the input range. For this dataset, the input range is D5:D20. Then Press OK.

As a result, you will see that the rows containing sales greater than $4,000,000 have gone to the bottom of the dataset.

Download Practice Workbook

You can download the practice workbook from here:

Move Row to Bottom.xlsm

Conclusion

In this article, you have found how to move row to bottom if a cell contains a specific value in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Move Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Move Row to Bottom in Excel If Cell Contains a Value

How to Move Row to Bottom If Cell Contains a Value: 2 Examples

1. Move Row to Bottom If Cell Contains a Specific Text

2. Move Row to Bottom If a Cell Contains Greater Than a Number

Conclusion

Related Articles

Leave a reply Cancel reply