CODEWITHSUNDEEP
vbaexcelhyperlink
Chase
Chase

Reputation: 584

VBA: Convert populated cells in columns to hyperlinks

I have a SQL script as a data connection in Excel that essentially returns 3 columns. These 3 columns are all the text of hyperlinks. These 3 columns can contain nulls / empty cells.

I found a VBA script to run that converts these hyperlink texts into actual hyperlinks (as in you can click on the cell and it will take you to the webpage).

My problem is: The VBA script is converting empty / null cells into hyperlinks as well (that of course go nowhere, i.e., you click on the cell and it simply does nothing).

My question is: How do I edit this script to only convert non-empty / populated cells into hyperlinks?

Script so far:

Sub HyperlinkConverter()
For Each xCell In Range("M:O")
    ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
End Sub

Pseudo-script:

Sub HyperlinkConverter()
For Each xCell In Range("M:O") **WHERE RANGE("M:O") IS NOT NULL (or) RANGE("M:O") <> ""**
    ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
End Sub

Upvotes: 0

Views: 4687

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Just test each cell:

Sub HyperlinkConverter()
   For Each xCell In Range("M:O")
      If xCell.Value <> "" Then
         ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
      End If
   Next xCell
End Sub

EDIT#1:

To avoid the first row:

Sub HyperlinkConverter()
   For Each xCell In Range("M:O")
      If xCell.Value <> "" And xCell.Row <> 1 Then
         ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
      End If
   Next xCell
End Sub

Upvotes: 2

Related Questions