求助：Excel VBA宏完成工作表筛选后，无法点击选中区域超链接的解决方法

Hey there! Let's break down why your hyperlink-following code isn't working and fix it up.

The Issue #

Your problematic line Selection.Hyperlinks(1).Follow... assumes that Selection is a single cell with a hyperlink, but when you use SpecialCells(xlCellTypeVisible) after filtering, you're likely selecting a multi-cell range (possibly even non-contiguous). Trying to access Hyperlinks(1) directly on this range will either fail (if the first cell in the selection has no hyperlink) or only act on the first cell, not all visible hyperlink cells.

Fix 1: Follow All Visible Hyperlinks #

If you want to click every hyperlink in the visible cells of column L, use a loop to iterate through each cell and check for hyperlinks first:

Sub FilterBasedOnCellValueAnotherSheet()
 Dim category As Range
 Dim LR As Long
 Dim cell As Range
 
 With Worksheets("Result")
     Set category = .Range("B3")
 End With
 
 With Worksheets("Summary")
     ' Apply filter
     With .Range("B3:M300")
         .AutoFilter Field:=1, Criteria1:=category, VisibleDropDown:=True
     End With
     
     ' Get last row in column B (use . to reference the Summary sheet explicitly)
     LR = .Range("B" & .Rows.Count).End(xlUp).Row
     
     ' Loop through each visible cell in column L
     For Each cell In .Range("L3:L" & LR).SpecialCells(xlCellTypeVisible)
         ' Only follow if the cell actually has a hyperlink
         If cell.Hyperlinks.Count > 0 Then
             cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
         End If
     Next cell
 End With
End Sub

Fix 2: Follow Only the First Visible Hyperlink #

If you only need to click the first visible hyperlink in column L, target that specific cell instead of selecting the whole range:

Sub FilterBasedOnCellValueAnotherSheet()
 Dim category As Range
 Dim LR As Long
 Dim firstVisibleCell As Range
 
 With Worksheets("Result")
     Set category = .Range("B3")
 End With
 
 With Worksheets("Summary")
     ' Apply filter
     With .Range("B3:M300")
         .AutoFilter Field:=1, Criteria1:=category, VisibleDropDown:=True
     End With
     
     ' Get last row in column B
     LR = .Range("B" & .Rows.Count).End(xlUp).Row
     
     ' Safely get the first visible cell (handle case where no cells are visible)
     On Error Resume Next
     Set firstVisibleCell = .Range("L3:L" & LR).SpecialCells(xlCellTypeVisible).Cells(1)
     On Error GoTo 0
     
     ' Follow the hyperlink if we found a valid cell with one
     If Not firstVisibleCell Is Nothing Then
         If firstVisibleCell.Hyperlinks.Count > 0 Then
             firstVisibleCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
         End If
     End If
 End With
End Sub

Key Improvements #

• Avoided using Select (it's unreliable and dependent on the active sheet)
• Added checks to ensure we only interact with cells that actually have hyperlinks (prevents "Subscript out of range" errors)
• Used . before Range and Rows to explicitly reference the Summary worksheet (no more accidental use of the active sheet)

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