Losing Hyperlinks in Adv Filters

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hi, hopefully someone out there can help with this one!

I am using the small macro below to perform an advanced filter on a
list.
I'm using a combo box to select the value and run the macro and
everything works fine. The data copies across to the copy to the new
range perfectly....Except for one thing!
My list has one column which are all Hyperlinks. When the filter
copies this data to the new location the Hyperlink is lost. The
formatting is preserved, ie: it is still blue and underlined, but the
link has disapeared.
If i change the filter from xlFilterCopy to xlFilterInPlace it works
fine, but i need to filter and move the data.
Any suggestions??

Sub AdvFilter

Range("A5:D579").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"D1:D2"), CopyToRange:=Range("F8:I8"), Unique:=False
End Sub

Cheers,
JB
 
It looks like if you copy and paste, the hyperlinks are kept. So how about
filtering in place, then copying and pasting?

This seemed to work ok. (I find it easier to change the range addresses if
they're assigned to a range variable. And you can copy|Paste the code to other
projects and just have to look at the Set statements.)



Option Explicit
Sub AdvFilter()

Dim myBigRng As Range
Dim myCritRng As Range
Dim myToRng As Range
Dim myFilterRng As Range

With Worksheets("sheet1")
Set myBigRng = .Range("a5:d579")
Set myCritRng = .Range("d1:d2")
Set myToRng = .Range("F8:i8")

myBigRng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=myCritRng, unique:=True

Set myFilterRng = myBigRng.Cells.SpecialCells(xlCellTypeVisible)

myFilterRng.Copy _
Destination:=myToRng

.ShowAllData

End With

Application.CutCopyMode = False

End Sub
 
Back
Top