Automate the Creation of an Index in Excel using VBA: Generate Hyperlinks within the Workbook for Easy Navigation

Sub CreateHyperlinkIfValuesMatch()

    Dim indexSheet As Worksheet
    Dim statisticsSheet As Worksheet
    Dim indexCell As Range
    Dim statisticsCell As Range
    Dim linkAddress As String
    
    Set indexSheet = ThisWorkbook.Sheets("Sheet A") ' change the sheet name to the name of your "Index" sheet
    Set statisticsSheet = ThisWorkbook.Sheets("Sheet B") ' change the sheet name to the name of your "Statistics" sheet
    
    For Each indexCell In indexSheet.Range("A2:A97")
        If Not IsEmpty(indexCell) Then
            For Each statisticsCell In statisticsSheet.Range("A2:A97")
                If indexCell.Value = statisticsCell.Value Then
                    linkAddress = "'" & statisticsSheet.Name & "'!" & "A" & statisticsCell.Row
                    indexSheet.Hyperlinks.Add Anchor:=indexCell, Address:="", SubAddress:=linkAddress, TextToDisplay:=statisticsCell.Value
                    
                    Exit For ' no need to check the rest of the cells in column A if a match has been found "Link to " & statisticsSheet.Name & "!" & "A" & statisticsCell.Row

                End If
            Next statisticsCell
        End If
    Next indexCell
End Sub

 

Automate Your Data Cleaning with VBA: A Step-by-Step Guide to Deleting Blank Cells in Excel

Sub DeleteBlankCells()
    Dim rng As Range
    Dim cell As Range
    Dim foundBlank As Boolean
    
    Set rng = Selection ' Set the range to the selected cells
    
    ' Check if the range contains any blank cells
    foundBlank = False
    For Each cell In rng
        If IsEmpty(cell) Then
            foundBlank = True
            Exit For
        End If
    Next cell
    
    ' If no blank cells were found, display a message and exit the macro
    If Not foundBlank Then
        MsgBox "No blank cells found in selection.", vbInformation
        Exit Sub
    End If
    
    ' Loop through each cell in the range
    For Each cell In rng
        If IsEmpty(cell) And Not IsEmpty(cell.Offset(-1, 0)) Then ' Check if the current cell is blank and the cell above is not
            cell.Delete Shift:=xlUp ' Delete the current cell and shift the cells up
        End If
    Next cell
End Sub

By saving your VBA code in the Personal Workbook, you can easily access it anytime you need it. You can also create a dedicated tab in Excel's ribbon and assign a button to the macro for even quicker access, streamlining your workflow and saving valuable time.