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.

1. 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:

2. Video: Select Only Cells with Data

This Excel video tutorial, we’ll show you how to select only cells with data, leaving out the blanks, using a VBA macro.