Open Multiple Hyperlinks based on autofilter results in Excel VBA

Question

Edit #1 I am trying to open multiple hyperlinks. All hyperlinks are for .pdf files. The hyperlinks are not sorted i.e. a cell preceding or succeeding a hyperlink might not be a hyperlink. I am using a autofilter based search. Out of let's say 100 visible rows form the autofilter, 60 of them have hyperlinks in a designated column. I would like to open all these files using Excel VBA.

I have been using

    Sub tobedeleted()

Dim Selrng As Range
Dim srch_cr As Variant

Set Selrng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

srch_cr = Application.WorksheetFunction.Transpose(Selrng)

    Dim hl As Hyperlink
    On Error Resume Next
    For Each hl In srch_cr.Hyperlinks
        hl.Follow
    Next hl

    For Each hl In ActiveWorksheet.Cells.SpecialCells(xlCellTypeVisible).Hyperlinks
    Next hl
End Sub

I have not been able to open these Hyperlinks.

Also the Designated column uses the Excel function hyperlink and I am basically using a vlookup to get the file location from a dump in another sheet. The Hyperlink function in the column activates the hyperlink.

FreeMan FreeMan · Accepted Answer · 2015-03-27T18:20:22

A good start would be to get rid of On Error Resume Next and see what errors your code is throwing.

Also, you say you are using AutoFilter to find the rows, but your code is running against Selection. AutoFilter doesn't automatically select the rows. You would need to use

For each hl in Worksheet().Cells.SpecialCells(xlCellTypeVisible).hyperlinks

Based on the discussion in comments, I think there are several issues:

I'm not sure why you're using Application.WorksheetFunction.Transpose(), as it doesn't seem to buy you anything. There's no telling what shape range your user might select, and reshaping it doesn't seem to me to be helpful.
Change your loop to For each hl in selrng.hyperlinks
- you're currently trying to use variant.hyperlinks, when .hyperlinks is an object of range

If that doesn't help, I'm fresh out of ideas.

Open Multiple Hyperlinks based on autofilter results in Excel VBA

1 Answers