[Excel VBA] How to Process Only Visible Cells (Excluding Hidden Rows and Filters)

2025年12月29日

In Excel, rows and columns are often hidden by AutoFilter or manual hiding. In such cases, you often want to process only the cells that are currently visible.

In VBA, you can use SpecialCells(xlCellTypeVisible) to extract and manipulate only the visible cells.

Code Example: Get the Address of Visible Cells

Sub ShowVisibleAddress()
    Range("C2:G12").Select
    MsgBox Selection.SpecialCells(xlCellTypeVisible).Address
End Sub

Explanation of the Code

1. Range(“C2:G12”).Select

This selects the target range (from C2 to G12). This range includes rows or columns that might be hidden.

2. Selection.SpecialCells(xlCellTypeVisible)

This extracts only the actually displayed cells (visible cells) from the selection.

Rows hidden by AutoFilter or manually hidden rows/columns are excluded.

3. .Address

You can check the range address of the visible cells in a message box.

Usage Scenario

Imagine the following situation:

Range: Columns C to G, Rows 2 to 12.
Column E is hidden.
Row 5 and Row 7 are hidden.

If you run the code above, it will display the address of only the visible cells, excluding the hidden parts.

Application: Bold Only Visible Cells

You can apply formatting only to the extracted visible cells.

Dim visibleCells As Range
Set visibleCells = Selection.SpecialCells(xlCellTypeVisible)
visibleCells.Font.Bold = True

This technique can also be used for changing cell colors, adding borders, or updating values.

Important Notes and Error Handling

Item	Description
Hidden areas	`xlCellTypeVisible` completely ignores hidden cells.
Error Handling	If there are no visible cells in the selection, an error will occur.
AutoFilter	This is perfect for processing data filtered by AutoFilter.

Error Handling Code Example

To prevent errors when no visible cells are found, use On Error Resume Next.

Sub ColorVisibleCellsSafe()
    On Error Resume Next
    Dim visCells As Range
    
    ' Attempt to get visible cells
    Set visCells = Selection.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    ' Check if visible cells were found
    If visCells Is Nothing Then
        MsgBox "No visible cells found."
    Else
        visCells.Interior.Color = RGB(230, 255, 230)
    End If
End Sub

Summary

Action	Syntax / Code
Extract visible cells	`SpecialCells(xlCellTypeVisible)`
Apply format	`visibleCells.Font.Bold = True`
Error Handling	`On Error Resume Next`

By using SpecialCells(xlCellTypeVisible), you can limit your VBA processing to only the cells the user can see. This is a highly practical technique for handling filtered data or bulk processing that ignores hidden cells.

よかったらシェアしてね！