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

Excel File Recovery, Excel Solutions June 5, 2018

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.Trigger "Go To"
  3. In the “Go To” dialog, click “Special” button.
  4. Next, check the “Constants” option and then “Numbers”, “Text”, “Logicals” and “Errors” options.Check Options in "Go To Special"
  5. Finally, click “OK”.
  6. When dialog box closes, as you see, all non-blank cells have been selected.Selected Non-blank Cells

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

VBA Code - Select All Non-blank Cells

  1. After that, exit the VBA editor and add this macro to Quick Access Toolbar.
  2. Now, open your desired worksheet and click the macro button.
  3. At once, all non-blank cells will be selected, as shown in the following image.Selected Cells via VBA Code

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 file is prone to errors and 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 reliable xlsx fix tool, 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, including SQL Server recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.