2 Simple Methods to Select All Non-blank Cells in an Excel Worksheet

In an Excel worksheet, if data is entered in nonadjacent cells, you may discover it a bit troublesome to select and copy the data in non-blank cells. Now, in this post, we will introduce 2 ways to quickly select all non-blank cells.

Excel users are frequently required to copy and paste data. However, sometimes, there may be some troubles. For example, there are multiple discontinuous blank cells in an Excel worksheet. In this case, it is a bit hard to select all the cells which have content. But, don’t worry. Here we will share you 2 approaches to select all non-blank cells in an Excel sheet.

Method 1: Select via “Go To Special”

1. At the outset, open the Excel worksheet.
2. Then, press “F5” to trigger “Go To” dialog box.
3. In the “Go To” dialog, click “Special” button.
4. Next, check the “Constants” option and then “Numbers”, “Text”, “Logicals” and “Errors” options.
5. Finally, click “OK”.
6. When dialog box closes, as you see, all non-blank cells have been selected.

Method 2: Select with Excel VBA

1. First off, get access to Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
2. Then, put the following code into an unused module.

Sub SelectAllNonBlankCells() Dim objUsedRange As Range Dim objRange As Range Dim objNonblankRange As Range Set objUsedRange = Application.ActiveSheet.UsedRange For Each objRange In objUsedRange If Not (objRange.Value = "") Then If objNonblankRange Is Nothing Then Set objNonblankRange = objRange Else Set objNonblankRange = Application.Union(objNonblankRange, objRange) End If End If Next If Not (objNonblankRange Is Nothing) Then objNonblankRange.Select End If End Sub

3. 在那之后,退出VBA editor and add this macro to Quick Access Toolbar.
4. Now, open your desired worksheet and click the macro button.
5. At once, all non-blank cells will be selected, as shown in the following image.

Comparison

	Advantages	Disadvantages
Method 1	Easy to operate	Users have to manually open “Go To” dialog box and check options every time when they need to select non-blank cells
Method 2	Easy and convenient for reuse	Arise the dangers of external malicious macros

Repair Annoying Excel Troubles

容易出现错误和Excel文件corruption. For instance, if MS Excel is frequently closed improperly, the file can get damaged with ease. Hence, users have to make some precautions, including backing up the files on a periodical basis. In addition, a proficient, robust and reliablexlsx fixtool, like DataNumen Excel Repair, is a matter of necessity.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, includingSQL Server recoveryand outlook repair software products. For more information visitwww.circareview.com