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.
How to Shift Cells Up in Excel: 5 Quick Ways
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 Down in Excel
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 option.
Step 3:
- Finally, select the Shift Cell Up option.
- Press Enter.
- As a result, you will see that the cells will be moved upwards
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 feature.
Step 3:
- Secondly, click on the Icon.
- Unmark the Blanks option.
- Finally, press Enter.
- Consequently, your all blanks in range will disappear and the cell will be moved up.
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 feature.
- Choose the Go To Special option.
Step 3:
- Then, select the Blanks option.
- Press Enter.
Step 4:
- Click on a blank cell and click the Right-click.
- Choose the delete option.
Step 5:
- Finally, choose the Shift cells up option.
- Press Enter to see the results.
- As shown in the below image, the cells will be shifted.
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 option.
- 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 shown in the image below.
Read More: How to Move Down One Cell Using Excel VBA
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
