Remove Blank Rows

How to remove blank rows from data in an Excel worksheet without VBA. Watch the video below:

Remove Blank Rows without VBA

We have learnt earlier how to remove blank rows to clean our data using VBA. We had also studied how to copy non blank data from sheet1 to sheet2. In another training video we saw how we can delete blank rows using a simple ‘for next’ loop.

Today we will learn how to remove the blank rows without using VBA. We will use the concept of ‘constants’ in Excel. Constants are data in our Excel worksheet which don’t contain any formula/function or whose values do not change with variation in other data in the worksheet. To view the constants in spreadsheet quickly, we navigate to Home tab and under the Editing group, we click on the drop down arrow next to Find & Select and from the options offered, we click Constants.

We can also click on ‘Go To Special…’ from the Find & select options in the Editing Group and in the new window ‘Go To Special’ that pops up, we select ‘constants’ as shown in the image below and finally click OK.

Go To Special Window
Go To Special –> Constants
Constants Selected

Here we can observe that all the data is selected except the cells or ranges with a formula whose values depent on ‘Sales’ and ‘Qty’ columns.