|
|
|
| Title | Use VBA code to make hyperlinks that jump to the first row beginning with specific letters |
|---|
| Description | This example shows how to use VBA code to make hyperlinks that jump to the first row beginning with specific letters |
|---|
| Keywords | VBA, Excel, hyperlink, letter, index |
|---|
| Categories | Office, Miscellany |
|---|
|
|
|
The code loops through the worksheet's rows. When it finds a row with first letter that is different from the previous row's first letter, it adds a hyperlink leading to that row.
|
|
Sub MakeLetterLinks()
Dim work_sheet As Worksheet
Dim last_letter As String
Dim next_letter As String
Dim r As Integer
Dim c As Integer
Set work_sheet = ActiveSheet
work_sheet.Hyperlinks.Delete
c = 0 ' Make the next hyperlink in column
' 1.
last_letter = "" ' Make the next letter different
' from this.
For r = 2 To work_sheet.UsedRange.Rows.Count
' Get the first letter in the next row.
next_letter = Left$(Cells(r, 1), 1)
' See if this is a new letter.
If last_letter <> next_letter Then
' Make a hyperlink.
last_letter = next_letter
c = c + 1
work_sheet.Hyperlinks.Add Cells(1, c), _
Address:="", _
SubAddress:="A" & Format$(r), _
ScreenTip:="Go to " & last_letter, _
TextToDisplay:="<" & last_letter & ">"
End If
Next r
' Center the hyperlinks.
work_sheet.Columns(1).HorizontalAlignment = xlCenter
End Sub
|
| |
|
| |
|
|
