VBA & Macro Problems

Excel VBA Macro to Create Hyperlinks from Adjacent Cells

Maira Mehtab Sep 28, 2026 872 views

Question details

The user needs an automated way to take web addresses located in one column and generate active hyperlinks in the immediately adjacent cells to the right.

Product: Excel
Device & OS: not provided
Scenario: Automating the batch creation of clickable hyperlinks in a spreadsheet based on plain text URLs listed in a neighboring column.
Observed behavior: The goal is to use a VBA script to dynamically link each target cell (e.g., G12) to the corresponding URL value in the adjacent cell (e.g., F12) across all populated rows without manual entry.

Before you start

Ensure you have enabled the Developer tab in your spreadsheet application to access the VBA Editor, and verify that your source text contains valid web addresses (e.g., starting with http:// or https://).

Solution 1Recommended

Use a VBA Loop to Create Hyperlinks in Adjacent Cells

This VBA macro loops through each populated row in a target column, checks the adjacent cell for a URL, and automatically generates an active hyperlink.

The provided VBA script uses the Application.ScreenUpdating property to ensure the macro runs quickly without screen flickering. It determines the last populated row in the source column and applies the Hyperlinks.Add method sequentially.

Open the VBA Editor

Open your Excel workbook and press 'ALT + F11' on your keyboard to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu bar, and then select 'Module' to open a blank code window.

Paste the VBA Code

Copy and paste the following code into the module window: Sub AddHyperlinks() Dim rng As Range Application.ScreenUpdating = False For Each rng In Range("G1:G" & Cells(Rows.Count, "F").End(xlUp).Row) If rng.Offset(0, -1).Value <> "" Then ActiveSheet.Hyperlinks.Add Anchor:=rng, Address:=rng.Offset(0, -1).Value End If Next rng Application.ScreenUpdating = True End Sub

Run the Macro

Close the VBA Editor to return to your worksheet. Press 'ALT + F8', select 'AddHyperlinks' from the list of macros, and click 'Run'. The macro will instantly create hyperlinks in Column G based on the web addresses in Column F.

Customizing the Columns: If your URLs are located in a different column, you must adjust the 'G' and 'F' references in the code. The 'Offset(0, -1)' parameter tells the macro to look exactly one column to the left for the URL address.

Use WPS Spreadsheet for VBA Tasks

Automate Hyperlink Creation with WPS Spreadsheet Macros

WPS Spreadsheet provides robust support for VBA macros, allowing you to seamlessly run Excel-compatible scripts to automate repetitive tasks like generating bulk hyperlinks.

1. Open the File in WPS: Launch WPS Office and open your spreadsheet document.
2. Access the Macro Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macro' > 'Visual Basic Editor'.
3. Paste and Execute: Insert a new module, paste your hyperlink VBA code, and press 'Run' to automate your workflow instantly.

Fully compatible with Microsoft Excel VBA syntax and functionsNative support for opening, editing, and saving Macro-Enabled Workbooks (.xlsm)Lightweight architecture ensures fast execution even on large datasetsFree to use with a familiar, easy-to-navigate user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I adapt this macro for different column letters?

Yes, you can easily change the column letters in the VBA code. For example, if your URLs are in Column A and you want links in Column B, update the range reference from 'G1:G' to 'B1:B' and change 'F' to 'A'.

Why are my hyperlinks not working after running the macro?

Ensure the URLs in your source column are complete and properly formatted (including 'http://' or 'https://'). If the text is just a domain name without a protocol, the operating system might fail to resolve it as a web address.

How do I save my workbook after adding this macro?

You must save your file as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the VBA macro code will be permanently deleted upon closing.

Will this macro overwrite existing data in the destination column?

Yes, the 'ActiveSheet.Hyperlinks.Add' method will apply the hyperlink and formatting to the destination cell, which will replace any text previously present in those specific cells.

Excel & Data Problems