How to Select Only Cells with Data in Excel

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.
Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

select cell with data1
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.

select cell with data2

Step6:choose one range that you want to select,such as: A1:B8

select cell with data3

Step7: let’s see the result:

select cell with data4

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.

Leave a Reply