VBA to add hyperlink function to each cell in a column

Question

Hello and thank you in advance for your help. Currently I created the Macro below that updates the range with a hyperlink function. This works great. However, the issue is that this data gets refreshed via a datasource. Once refreshed there are blanks in Column A. Is there a way to execute this when a refreshed datasource occurs?

Sub InsertHyperlinkFormulaInCell()

ActiveWorkbook.Worksheets("Query").Range("A2:A20000").Formula = 
"=HYPERLINK(CONCAT(x2,B2),W2)"

End Sub

Any help would be greatly appreciated. Thank you!

fixnomal · Accepted Answer · 2020-05-05 05:26:26Z

1

you need to loop through the rows to change the hyperlink in every row otherwise you're just changing row 1. If you want to keep the Excel reference in the sheet you can just exchange the row number in a for loop (or in this case while loop) which is sometimes nice to have but it makes for clunky code in the concatenation process.

Sub InsertHyperlinkFormulaInCell()

    'starting with row 2 since row 1 is title row
    currentRow = 2

    ' cells allows to call columns by number instead of letters (so A becomes 1, B:2 etc.)
    While Cells(currentRow, 2) <> "" 'check whether Column B is empty, stop if it is
        'This is your active line, just replaced the row number 1 with the variable currentRow
        'which loops through the sheet until it encounters and empty cell in column B
        ActiveWorkbook.Worksheets("Sheet1").Cells(currentRow, 1) = "=HYPERLINK(CONCAT(B" & currentRow & ",C" & currentRow & "),D" & currentRow & ")"
        currentRow = currentRow + 1
    Wend

End Sub

Hope that helps

answered May 5, 2020 at 5:26

fixnomal

1051 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

fixnomal Over a year ago

Actually, the hyperlink line can just be changed to =HYPERLINK(CONCAT(RC[1],RC[2]),RC[3]). If you prefer the letter column style excel will automatically change it. However, in the R1C1 style (columns are numbered) the formula is the same for every row so that makes it a lot less clunky.

Adam Over a year ago

I must have updated my script right when you posted this. However, yours is so much cleaner! Thank you!

Adam Over a year ago

Is there a way to execute this after a refresh All is clicked/completed on a datasource? That was also part of my edit/update. Thanks again!

fixnomal Over a year ago

Not sure how your update occurs but usually the way to add a function that executes on change is to add the sub "Private Sub Worksheet_SelectionChange(ByVal Target as Range)" to you worksheet-> Go to your VB window, double click on your "Query" worksheet and select worksheet and selectionChange, that should create it. Paste or call your code from here and it should trigger whenever there's a change in the worksheet.

fixnomal Over a year ago

If you want to limit the range that triggers your code insert an if statement on the target variable. For example if you want it to only run if there's a change in column A: if Target.column = 1 then InsertHyperlinkFormulaInCell().

|

Collectives™ on Stack Overflow

VBA to add hyperlink function to each cell in a column

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related