This post will guide you how to select all cells with data in Excel. How do I select only cells with data using VBA Macro in Excel 2013/2016.
Select Only Cells with Data
Assuming that you have a list of data in range A1:B5, in which contain text values and blank cells, and you want only select all cells with data or text values, how to do it. You can use an Excel VBA Macro to select all cells with data. Just do the following steps:
Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Step2: then the “Visual Basic Editor” window will appear.
Step3: click “Insert” ->”Module” to create a new module.
Step4: paste the below VBA code into the code window. Then clicking “Save” button.
Sub SelectCellsWithData() Dim bCell As Range Set myRange = Application.Selection Set myRange = Application.InputBox("choose one range that you want to select:", "SelectCellsWithData", myRange.Address, Type:=8) For Each myCell In myRange If myCell.Value <> "" Then If bCell Is Nothing Then Set bCell = myCell Else Set bCell = Union(bCell, myCell) End If End If Next If Not bCell Is Nothing Then bCell.Select End If End Sub
Step5: back to the current worksheet, then run the above excel macro. Click Run button.
Step6:choose one range that you want to select，such as: A1:B8
Step7: let’s see the result: