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.

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

Related Posts

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing ...

VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

Comments

So empty here ... leave a comment!

Leave a Reply

Sidebar