Hi all,

I found this code which gives you the full URL of a shortened (e.g. bitly) link:

Public Function unwrap(url As String) As String
    
    Static oRequest As Object
    
    Set oRequest = CreateObject("WinHTTP.WinHTTPRequest.5.1")
    
    With oRequest
        .Option(6) = True
        .Option(12) = True
        .Open "HEAD", url, False
        .send
        unwrap = .Option(1)
    End With
    
End Function
You basically use this on a cell by cell basis via =unwrap(A1) formula.

I have a fixed range of shortened URLs (that change dynamically) that I would like to get unwrapped one by one, but only at the click of a button. So ideally, this wouldn't be a public function, but a simple sub that I can manually execute on a specified range of cells.
Anyone has an idea how to do that? Tried a few things, but totally lost :-(

Hope you can help?
Thanks!