If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
pulling a list of hyperlinks from a worksheet
I have a worksheet with over 800 cells. Each cell displays a churches
website url. I need to display the actual hyperlink http://www.whatever.com not the Link name. example First Baptist Church Nashville. I need the hyperlink http://fbcnashville.org. displayed in the next cell. |
#2
|
|||
|
|||
pulling a list of hyperlinks from a worksheet
'Here's a short macro:
'Note that this assumes you are not using the function HYPERLINK, but just an 'embedded hyperlink 'To install, right click on sheet tab, view code, paste the following in. Sub ListHyperlinks() i = 1 For Each h In Me.Hyperlinks 'Generates list in column Z, starting at row 1 Cells(i, "Z").Value = h.Name i = i + 1 Next h End Sub -- Best Regards, Luke M "Dale" wrote in message ... I have a worksheet with over 800 cells. Each cell displays a churches website url. I need to display the actual hyperlink http://www.whatever.com not the Link name. example First Baptist Church Nashville. I need the hyperlink http://fbcnashville.org. displayed in the next cell. |
#3
|
|||
|
|||
pulling a list of hyperlinks from a worksheet
If you have this http://fbcnashville.org/
typed into a cell you can just use Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) ActiveWorkbook.FollowHyperlink Address:=Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Dale" wrote in message ... I have a worksheet with over 800 cells. Each cell displays a churches website url. I need to display the actual hyperlink http://www.whatever.com not the Link name. example First Baptist Church Nashville. I need the hyperlink http://fbcnashville.org. displayed in the next cell. |
#4
|
|||
|
|||
pulling a list of hyperlinks from a worksheet
See if this doesn't do the job for you:
Sub ExtractHyperlinks() 'change these Const values as needed Const theWorksheetName = "Salary.com" 'the column with hyperlink text Const hLinkColumn = "B" 'column to put link only into Const hLinkOnlyCol = "AI" Dim myWS As Worksheet Dim linksRange As Range Dim anyLink As Range Set myWS = Worksheets(theWorksheetName) Set linksRange = myWS.Range(hLinkColumn & "1:" & _ myWS.Range(hLinkColumn & Rows.Count).End(xlUp).Address) For Each anyLink In linksRange If anyLink.Hyperlinks.Count 0 Then myWS.Range(hLinkOnlyCol & anyLink.Row) = _ anyLink.Hyperlinks(1).Address End If Next Set linksRange = Nothing Set myWS = Nothing MsgBox "Task Completed" End Sub "Dale" wrote: I have a worksheet with over 800 cells. Each cell displays a churches website url. I need to display the actual hyperlink http://www.whatever.com not the Link name. example First Baptist Church Nashville. I need the hyperlink http://fbcnashville.org. displayed in the next cell. |
Thread Tools | |
Display Modes | |
|
|