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.

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.