如何让VBA代码在筛选条件无数据点时跳过Application.Intersect函数？

Hey there, let's break down why your existing checks aren't working and fix that error in lines 3-4!

The Root of the Problem #

Your two IsError attempts aren't doing the trick because IsError only detects cell error values (like #N/A or #VALUE!), not the runtime error thrown by SpecialCells(xlCellTypeVisible) when filtering leaves no visible cells. When that happens, the code crashes before your IsError check even gets to run.

The Solution: Error Handling + Object Existence Check #

We can use VBA's error handling to catch that runtime error, then verify if our target range exists before proceeding. Here's how to adjust your code:

Dim filterRange As Range
Dim visibleDataRange As Range
Dim lastRow As Long

' First, define your filter range (replace with your actual last row logic)
lastRow = Worksheets("JO_Pickups (Test)").Cells(Rows.Count, "A").End(xlUp).Row
Set filterRange = Worksheets("JO_Pickups (Test)").Range("$A$1:$E$" & lastRow)

' Catch the error that occurs when no visible cells exist
On Error Resume Next
' Get the visible data rows (offset(1,0) skips the header row)
Set visibleDataRange = Intersect(filterRange, filterRange.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0 ' Reset error handling to normal

' Now check if we successfully got a visible range
If Not visibleDataRange Is Nothing Then
    ' Put your original code that uses the intersect range here
    ' Example:
    MsgBox "Found visible data at: " & visibleDataRange.Address
Else
    ' No matching data? Skip the Intersect-related logic
    Debug.Print "No visible data found - skipping Intersect operation."
End If

Key Notes: #

• On Error Resume Next temporarily tells VBA to skip over errors (like the no-visible-cells error) instead of crashing.
• On Error GoTo 0 is critical—it restores normal error handling so any other bugs in your code don't get hidden.
• Checking If Not visibleDataRange Is Nothing confirms whether we actually found visible cells to work with.

内容的提问来源于stack exchange，提问作者Pherdindy