Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test if cell contains hyperlink need help (excel2000+)

    Thought I could do the rest but am stuck!
    I have one column of data which contains hyperlinks and cells which contain data.
    I want to copy all the data between each hyperlink to a new row in a new spreadsheet.
    The hyperlinks can be one after the other or any unlimited rows between hyperlinks.
    eg: If hyperlinks appear one after another then I want to get 2 rows each starting with the specific hyperlink.
    If difference between hyperlinks is say three rows the I will get 2 new rows--column 1 containing hyperlink columns 2&3 data
    and next row starts with new hyperlink.(in a nutshell each hyperlink starts a new row)
    Thanx
    Smbs

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Test if cell contains hyperlink need help (excel2000+)

    Since this is a follow-up question to your previous one, it would have been better if you had asked it there. Your first sentence here is incomprehensible to Loungers who haven't read the other thread.

    Here is a macro that will do what you want. It uses the HasHyperlink function from <post#=434882>post 434882</post#>.

    Sub CopyToRows()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceStartRow As Long
    Dim lngSourceEndRow As Long
    Dim lngSourceRow As Long
    Dim lngSourceCol As Long
    Dim lngTargetRow As Long
    Dim lngTargetCol As Long

    ' *** Change as needed ***
    Set wshSource = Worksheets("Sheet1")
    Set wshTarget = Worksheets("Sheet2")

    lngSourceStartRow = 1
    lngSourceCol = 1
    ' ************************

    ' Determine last row
    lngSourceEndRow = wshSource.Cells(65536, lngSourceCol).End(xlUp).Row

    ' Loop through cells
    For lngSourceRow = lngSourceStartRow To lngSourceEndRow
    If HasHyperlink(wshSource.Cells(lngSourceRow, lngSourceCol)) Then
    ' Move to a new row
    lngTargetRow = lngTargetRow + 1
    lngTargetCol = 1
    Else
    ' Move to the next column
    lngTargetCol = lngTargetCol + 1
    End If
    ' Copy cell from source to target
    wshSource.Cells(lngSourceRow, lngSourceCol).Copy Destination:=wshTarget.Cells(lngTargetRow, lngTargetCol)
    Next lngSourceRow

    ' Clean up
    Set wshSource = Nothing
    Set wshTarget = Nothing
    End Sub

    See attached workbook.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if cell contains hyperlink need help (excel2000+)

    Hi Hans
    Sorry about no continuing in previous thread--my mistake and thanx for your solution.
    Regards
    Smbs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •