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”
- At the outset, open the Excel worksheet.
- Then, press “F5” to trigger “Go To” dialog box.
- In the “Go To” dialog, click “Special” button.
- Next, check the “Constants” option and then “Numbers”, “Text”, “Logicals” and “Errors” options.
- Finally, click “OK”.
- When dialog box closes, as you see, all non-blank cells have been selected.
Method 2: Select with Excel VBA
- First off, get access to Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
- 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
- After that, exit the VBA editor and add this macro to Quick Access Toolbar.
- Now, open your desired worksheet and click the macro button.
- 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 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
Just wish to say your article is as amazing. The clearness in your post is simply spectacular and i can assume you’re an expert on this subject. Well with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please carry on the enjoyable work.