Excel Guides

Selecting Visible Cells in a Macro in Excel

There are a few different ways to select visible cells in a macro in Excel. One way is to use the SpecialCells method. This method takes two arguments. The first argument is the type of cells that you want to select. The second argument is the cell reference from which to start the search.

The UsedRange property is another way to select visible cells in a macro. This property returns a Range object that represents all the cells that contain data in the worksheet. The UsedRange property doesn't include cells that are empty or that contain only formulas.

You can also use the Find method to select visible cells in a macro. The Find method searches for a specified string in a range of cells, and returns a Range object that represents the first cell where that string is found. You can use the After, Before, or Custom properties with the Find method to specify where the search should start.

Here is an example of how to use the SpecialCells method:
Sub SelectVisibleCells()

    Dim rng As Range

    'Select all visible cells in column A.
    Set rng = Range("A1").SpecialCells(xlCellTypeVisible)

    'Select all visible cells on the active sheet.
    Set rng = ActiveSheet.SpecialCells(xlCellTypeVisible)

    'Select all visible cells on Sheet1.
    Set rng = Sheets("Sheet1").SpecialCells(xlCellTypeVisible)

    'Select all visible cells in range A1:B10 on Sheet1.
    Set rng = Sheets("Sheet1").Range("A1:B10").SpecialCells(xlCellTypeVisible)
    
End Sub

Here is an example of how to use the UsedRange property:

Sub SelectUsedRange()

    Dim rng As Range

    'Select all used cells on Sheet1.
    Set rng = Sheets("Sheet1").UsedRange

    'Select all used cells on Sheet1, starting at cell A1.
    Set rng = Sheets("Sheet1").UsedRange.Cells(1)

    
End Sub

Here is an example of how to use the Find method:

Sub SelectFoundRange()

    Dim rng As Range

    'Select all used cells on Sheet1 that contain "test".
    Set rng = Sheets("Sheet1").UsedRange.Find("test")

    
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.