Bhubon Costa
If you have a list with blank cells, you may wish to move all data up to eliminate the blank cells. This article demonstrates how to delete blank cells and shift cells up in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Quick Ways to Shift Cells Up in Excel
We’ll show you 5 simple techniques to shift cells up in the sections below. Basic Excel commands and VBA codes are used in these procedures. An example data set is shown in the figure below with a blank in row 10. We must shift the cell up to take up the blank location.
1. Apply Drag to Shift Cells Up in Excel
Selecting and dragging cells is the simplest way to shift them around. To rearrange cells by dragging, follow the methods given below.
Step 1:
- Select the cells you want to shift.
Step 2:
- Hold the mouse Left–click, and shift upwards.
- Therefore, the cells will be shifted towards up.
Read More: How to Shift Cells Right in Excel (4 Quick Ways)
2. Use the Right-Click to Shift Cells Up in Excel
Right–clicking with the mouse may also be used to move cells up. Follow the instructions below to do so.
Step 1:
- Firstly, select the blank cells.
Step 2:
- Click on the Right-click to show the options.
- Choose the Delete
Step 3:
- Finally, select the Shift cell up option.
- Press Enter.
- As a result, you will see that the cells will be moved upwards
Read More: How to Move Rows Up in Excel (2 Quick Methods)
Similar Readings
- How to Move Highlighted Cells in Excel (5 Ways)
- Move One Cell to Right Using VBA in Excel (3 Examples)
- How to Rearrange Rows in Excel (4 Ways)
- Use the Arrows to Move Screen Not Cell in Excel (4 Methods)
- Fix: Excel Cannot Shift Nonblank Cells (4 Methods)
3. Apply the Sort & Filter Command to Shift Cells Up in Excel
You may also be able to shift cells upwards by eliminating blank cells using the Sort & Filter command. Follow the steps below to use the Sort & Filter command to move the cells upwards.
Step 1:
- First of all, select all cells in the range.
Step 2:
- From the Data tab, choose the Filter
Step 3:
- Secondly, click on the Icon.
- Unmark the Blanks
- Finally, press Enter.
- Consequently, your all blanks in range will be disappeared and the cell will be moved up.
Read More: How to Move Cells without Replacing in Excel (3 Methods)
4. Use the Find & Replace Option to Shift Cells Up in Excel
To shift the multiple cells up, we may utilize the Find & Replace option, which is similar to the previous way. Follow the steps outlined below to complete it.
Step 1:
- Select all the cells.
Step 2:
- Go to the Home tab and choose the Find & Replace
- Choose the Go To Special
Step 3:
- Then, select the Blanks
- Press Enter.
Step 4:
- Click on a blank cell and click the Right-click.
- Choose the delete
Step 5:
- Finally, choose the Shift cells up
- Press Enter to see the results.
- As shown in the below image, the cells will be shifted.
Read More: How to Move Down One Cell Using Excel VBA (with 4 Useful Applications)
5. Run a VBA Code to Shift Cells Up
So, the VBA code is also here to shift or move the cells up. Follow the procedure described here to do so.
Step 1:
- Firstly, press Alt + 11 to open the Macro.
- Click on the Insert
- Choose Module from the list.
Step 2:
- Paste the following VBA codes here.
Sub Shift_cells_up
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1) = 0 Then
Range("A" & iCntr).Delete Shift:=xlUp
End If
Next
End SubHere,
lRow = 20 refers to the total row in the range.
For iCntr = lRow To 1 Step -1 refers to IRow will be checked step by step.
If Cells(iCntr, 1) = 0 refers to the If condition for blank cells.
Range(“A” & iCntr). Is your range column
Delete Shift:=xlUp refers to deleting the rows but not the entire row and shifting cells up
- Therefore, the final result is being shown in the image below.
Read More: [Fixed!] Unable to Move Cells in Excel (5 Solutions)
Conclusion
To summarize, I hope this article has shown you how to delete blank cells and shift cells up in Excel. Examine the practice book and put your newfound knowledge to use. Because of your support, we are willing to pay for initiatives like these.
Please do not hesitate to contact us if you have any queries. Please leave a comment below to let me know what you think.
Experts from the Exceldemy Team will answer as quickly as possible to your inquiries.
