Recreating a Twitter Search URL in Excel

Hi Niels,

I updated a file, please see attached.

First about twitter limit:

Within any regular search, a maximum limit of 500 characters can be allowed in a search string, after the string is URL encoded (for example a space will take up 3 characters when encoded as ‘%20’ in the URL).

see at Advanced Twitter search using query operators

Thus in any case you have to be in above limit.

To generate hyperlink directly this formula is used

=IFERROR(
   HYPERLINK("https://twitter.com/search?q=" & F150 & " %23" & F151 & " from:"& GetQueryString(C2, " OR from:"), "Search Twitter"),
   "The query is too long")

with updated macro which automatically calculates the size of your range

Public Function GetQueryString(rng As Variant, seperator As String)

   Dim nRows As Long, rSearch as Range
With ActiveSheet
   nRows = .Cells(.Rows.Count, "C").End(xlUp).Row
   For Each cll In .Range(rng, .Cells(nRows,"C"))
      If cll.EntireRow.Hidden = False Then _
         GetQueryString = GetQueryString & cll.Value & seperator
   Next
   GetQueryString = Left(GetQueryString, Len(GetQueryString) - Len(seperator))
End With
End Function

Here the query is to be less than 255 characters, that's the limitation of HYPERLINK function.

To expand it a bit another two macros are added. First generates the hyperling in the cell after you click the button on which the macro assigned, after that you may click on this link

Sub HyperlinInCell()

Dim i As Long, firstRow As Long, lastRow As Long
Dim sLinkName As String, sHyperlink As String, sSeparator As String

firstRow = 2        'Put first hyperlink on this row
sSeparator = " OR from:"
sHyperlink = "https://twitter.com/search?q=" & Range("F150") & " %23" & Range("F151") & " from:"
sLinkName = "Twitter Search"

With ActiveSheet
   lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
   .Cells(153, "E").Value = ""

   For i = firstRow To lastRow
      If .Cells(i, "C").EntireRow.Hidden = False Then _
         sHyperlink = sHyperlink + .Cells(i, "C").Value + sSeparator
   Next
   sHyperlink = Left(sHyperlink, Len(sHyperlink) - Len(sSeparator))
    
   .Cells(153, "E").Value = sHyperlink
   If Len(sHyperlink) < 1024 Then
      .Hyperlinks.Add anchor:=.Cells(153, "E"), _
      Address:=sHyperlink, _
      TextToDisplay:=sLinkName
   Else
      MsgBox ("The query string is too long")
   End If

End With
End Sub

Another macro opens your default browser if you click on related button (and adds search string in the cell to the right of button)

Sub OpenDefaultBrowser()

Dim i As Long, firstRow As Long, lastRow As Long
Dim sLinkName As String, sHyperlink As String, sSeparator As String

firstRow = 2
sSeparator = " OR from:"
sHyperlink = "https://twitter.com/search?q=" & Range("F150") & " %23" & Range("F151") & " from:"
sLinkName = "Twitter Search"

With ActiveSheet
   lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
   .Cells(155, "E").Value = ""

   For i = firstRow To lastRow
      If .Cells(i, "C").EntireRow.Hidden = False Then _
         sHyperlink = sHyperlink + .Cells(i, "C").Value + sSeparator
   Next
   sHyperlink = Left(sHyperlink, Len(sHyperlink) - Len(sSeparator))
    
   If Len(sHyperlink) < 1024 Then
      .Cells(155, "E").Value = sHyperlink
      ThisWorkbook.FollowHyperlink Address:=.Cells(155, "E").Value
   Else
      MsgBox ("The query string is too long")
   End If

End With
End Sub

It looks like this

Latest two have the limit 1024 characters for the URL generated

Please note i didn't polish the code and hardcoded all you locations within the Excel, you may improve that.

Here it is